Wow, it has been a while since I’ve posted. It isn’t that I’ve been lazy but I’ve been busy doing things. I ran into this one that took me a bit of time to fix and wanted to post it for future reference.

SAME DAY payday loans

Cannot shrink log file 2 () because all logical log files are in use

Today while running a script in a dev environment, the log file grew to 135GB leaving only 9mb left on the log drive. When I tried to free the space back to the OS, it wouldn’t do anything. I tried to change the recovery mode, from SIMPLE to FULL, FULL to SIMPLE, SIMPLE to BULK and BULK to SIMPLE. In between each change, I would attempt to release the space back to the OS. But it wouldn’t budge.

Each time when I attempted to shrink, I would get the message:

Cannot shrink log file 2 () because all logical log files are in use.

I haven’t seen this message before so addition effort was exerted such as:

- taking the database offline and online again
- running dbcc checkdb
- checkpointing
- full backup of the database

I don’t know why I didn’t think of it earlier but I wanted to see what the log is stating, so I ran the following:

SELECT log_reuse_wait_desc FROM sys.databases WHERE NAME = ‘db_name’

The result said: REPLICATION

I was like WTF! REPLICATION?! I looked at replication monitor and did not see any replication. It seem that this damn database was restored from an environment where replication was setup. So I ran:

EXEC sp_removedbreplication ‘db_name’

It ended with an error but when I looked at the log_reuse_wait_desc again, it said: NOTHING. Once I saw this, I went ahead and release the space back to the OS and BAM, 135GB of space was recovered.

After reading about it for o so long and never really wanting to try it, I finally took a stab at it. The objective of what I wanted to do was to track used objects for all the databases on a server and what statement were making use of the objects.  The code started on a SQL 2012 but ended up on a SQL 2008 R2 instead.  For the downgrade to work, I had to make a slight modification.

If you haven’t worked with Extended Events, let me just warn you that using it on a live system will generate a lot of results within a few minutes and parsing thru all that data can be time consuming depending on the size of your file.  I started with 128mb and 2 rolling files.  It took a long time to parse thru.  I ended up with 2 rolling 5mb files at the end of the day. The code to do this for SQL 2012:

CREATE EVENT SESSION [ExtLockAcquire] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
 ACTION(sqlserver.database_id,sqlserver.sql_text)
 WHERE ([package0].[equal_uint64]([resource_type],(5)) AND [sqlserver].[is_system]=(0))) 
ADD TARGET package0.event_file(SET filename=N'C:\temp\ext\extLockAcquire.xel',max_file_size=(5),max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

The code for SQL 2008 R2:

CREATE EVENT SESSION [ExtLockAcquire] ON SERVER 
ADD EVENT sqlserver.lock_acquired(
 ACTION(sqlserver.database_id,sqlserver.sql_text)
 WHERE ([database_id] > 4
 and [database_id] <> 393)
 AND [package0].[equal_uint64]([resource_type],(5)) 
 AND [sqlserver].[is_system]=(0)) 
ADD TARGET package0.asynchronous_file_target(SET filename=N'S:\ext\extLockAcquire.xel',max_file_size=(5),max_rollover_files=(2))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Once this info is captured, you can use the following to query the xml file:

SELECT *
 n.value('(@name)[1]', 'varchar(50)') AS event_name
 , db_name(n.value('(data[@name="database_id"]/value)[1]', 'int')) as database_id
 , n.value('(data[@name="resource_0"]/value)[1]', 'int') as [object_id]
 , object_name(n.value('(data[@name="resource_0"]/value)[1]', 'int'), n.value('(data[@name="database_id"]/value)[1]', 'int')) as [object_name]
 , n.value('(action[@name="sql_text"]/value)[1]', 'varchar(500)') as sql_text
 , n.value('(@timestamp)[1]','datetime') as dttm
FROM 
(SELECT 
 cast(event_data as xml) as event_data
 FROM sys.fn_xe_file_target_read_file('C:\temp\ext\extLockAcquire*.xel', null, null, null)
) as tab
CROSS APPLY event_data.nodes('event') as q(n)

The meat of the query is the subquery. When executing on its own, you can see the actual XML and what other type of informations are present.

If the Extended Event session is running, the above query will NEVER end.  You have to first stop the session.

Now that I have all of the above, a SQL job was setup to log what I captured and store in into a reporting table for later reviewing.  The SQL job runs every 2 minutes and insert only data that doesn’t currently exist in the reporting table.

I was looking to take a differential backup today of a database that is pretty big, definitely not a VLDB but when disk is limited, I wanted to make sure that the differential will fit on the local disk.

I found the following site to be of help: dougzuck.com

Today, I was working on a ticket that require me to restore a report from SSRS sql 2008 R2.  The person that wrote the report doesn’t have a backup copy nor was it on a the dev instance.  Fun.

I didn’t find any backup files on the local drives so I asked another DBA and he found a copy on tape.  I restored the database and named it ReportServer24.  Fired up the Reporting Services Configuration Manager, and point SSRS to this new database ReportServer24.  All seem to have gone well except I got a message stating that it can’t find ReportServer24Tempdb.  I would go back to the other DBA and ask him to get me the SSRS tempdb as well except it would take forever.

So I just did a database backup of the original SSRS tempdb, ReportServerTempDB, and restore it back as ReportServer24TempDB.  This solved the problem and all was seemingly going well until we tried to access the report website: http://localhost/reports.

This little nasty error showed up: The report server installation is not initialized. (rsReportServerNotActivated).

To get around this, I went back into Reporting Services Configuration Manager and backed up the encryption key.  Once it was backed up, I deleted the encryption key.  From there, i was able to pull up the reports’ website.  From here, I would find the report, download it, and upload it to the production reporting server.

Except that the user didn’t give me the bloody name so I have to wait until tomorrow and the user can download it her self.

I’m sure there’s another way to recover the report via the catalog and such but I figured this way would be WAY faster (?).

Today, I was building out a new SQL 2005 server to replace another server.  This server will eventually house a SQL 2012 instance but we don’t have the time to test everything.  So the setup are identical.  Once everything was installed, patches applied, and SP4 loaded. I made a backup of the master, msdb, and the system resources database … just in case I need it at a later time.

After restoring the master database in single user mode, bringing up the SQL server and restoring the msdb database. I tried to create some linked server, change the security context to: ‘Be made using the login’s current security context‘, and ran into the following error:

no remote user ‘link_server’ mapped to local user ‘(null)’ from the remote server

I haven’t dealt with this type of error before because quite frankly, I never really had to restore the master database.  To resolve this, I’ve tried deleting and recreating the local account.  No go.  Verified that I had the right password, no go.  Checked that the remote server had the local account as well.   Everything was good.

So why the heck am I getting this error?!  Finally giving up, I took to the web and found the answer:

ALTER SERVICE MASTER KEY FORCE REGENERATE

More information about this syntax can be found here

“Make good things when you are young and you’ve covered your needs for life.” – Steve Wozniak.

This week, I was tasked with fixing two very long running queries.  The person that wrote it told me that it ran really fast at the start and has since gotten longer and longer.  The last time runtime were 10hrs and 18hrs.  What to do when you already know which sproc is the issue?

Well I got the estimated execution plan, looked thru all the indicators to see which one is causing a lot of problems and what kind of indexes I can put in to make it faster.  I was a bit suprised that the indexes were already in placed, they weren’t fragmented, and the statistics are up-to-date.

Now what?  If everything is in place, why is it taking so long?  I ran sp_helptext ‘sproc_name’ to see what it is doing.  What it was doing was basically taking a column and passing it into a function via a CROSS APPLY, which is good, except that the function was doing a bunch of stuff before returning the result.

I ran a portion of the SQL statements that I figured might be the problem and alas it was, as it took 99% of the time to complete.  This simple 6 lines of code basically select data from a few tables (using inner joins) and then storing it into a memory table for later processing.

I did some more investigation and found that again, everything was inline.  Then after lunch, on a full stomach, and a bit sleepy (damn you delicious lunch!!), I saw what the other queries in the function had that this main SQL statement didn’t!

All the other statement had WHERE isActiveAccount = 1 and process_number = @process_number but this one didn’t!  Why?  Why gather all these UNECESSARY data and not make use of it?  I went ahead and added the extra criteria in the WHERE clause.  The whole thing went from 10hrs to 3mins and 44seconds.

I was happy with it but one of my co-worker asked if I was REALLY happy.  And I wasn’t really happy but it is an improvement but I wanted a challenge … get it to run under 1min.  So I looked some more and found that the sproc was using a view that had a REPLACE and CAST statement on a column.  Well this sproc doesn’t need all that.  And looking at the definition of view, it was basically a SELECT * FROM TableA WHERE CAST(REPLACE(blah,’,',”) as float) <= ‘some value.’

Why waste time with this view when it isn’t needed?  I had the function look at the real table and was able to get this sucker down to 1:57seconds.  57 more seconds to trim.  The function was also using the LEFT function, it wasn’t needed so it was removed and that got it down to 1:32seconds.

I noticed that the function was collecting a large amount of data from a table and then cycling thru it.  I figured I can help it preprocess those data in a real table.  This real table is truncated everything the sproc is executed, once that is done, it will populate the necessary data.  The function will make use of it.

Afterward, the sproc completed in under 47seconds.  The other sproc, ran for 18hrs, was similar to this 10hrs one.  I was only able to get that to run in under 2mins.

As you may know, I’ve recently tweaked someone’s C# code to make it work the way I want.  That post can be found here.  I am pleased to say that it work perfectly on my laptop but when I loaded it on the server to be executed, I got the error:

Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly ‘Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c’ or one of its dependencies. The system cannot find the file specified.

Once again, I took to the web, looking for the answer.  I found links and articles, read them, tried them and still didn’t work.  Last night, I was talking to a ex co-worker at Microsoft who is a seasoned C# developer and I brought up the question.  He, of course, was in bed and I was pretty much talking to myself via MSN Live Messenger.

But this morning he responded to my question:

RD says (8:10 AM): look at your references in your project, and make sure all of them are set to copy local. Then when you deploy, deploy all the dlls with your exe.

you can find this by expanding ‘References’ –> click on the reference and go to its property.  There look for, Copy Local.  By default, this is set to False.  Change it to True.  Setting the references to True will tell Visual Studio to extract the DLL from where-ever the hell it is stored (actually, you can tell where it is stored) to the bin\debug or bin\release folder when the project is compiled.  All you have to do afterward is copy the freshly minted .exec, .dlls, and .config (if one is used) to any Windows machine and it will work.

Thanks RD.

Yesterday, I was trying to import a flat file into a sql table.  The table has varchars and ints.  I had to use a data conversion tool to convert to string and 4byte unassigned so it the data type would convert correctly.  Except, SSIS was being a big douche and didn’t want to be nice.  I kept getting errors about the data type coversion for client_id is invalid and blah blah blah.

I asked a coworker who has more experience than I do when it comes to SSIS pkgs and he suggested that I create a staging table with the columns being varchar.  Once the data is in there, I could create a view/sproc to import from the staging table to the actual table with converts/casts in place.  This step has made life a bit easier for me because I was trying to figure out why the hell SSIS 4-bytes unsigned/signed integer won’t get go into SQL’s int column nicely!

A few weeks ago, I was looking to export more than 255 columns from a table in SQL to Excel using SSIS.  I thought this task was going to a piece of cake because in SSIS, within the dataflow task, you get an Excel Destination task and this will take care of all my woes!  But alas, it didn’t and I have spent the past 2.5 weeks, not all 8hrs a day, looking for a solution.

The setup: A table has over 255 columns and I’m using office 2010.

I went all over the place looking for a solution.  I went to Microsoft’s social msdn site and asked for help (here and here).  Some attempted to help but none was able to provide a solution.  I even went on to ask on #ssis on twitter and no one responded, which really sucked ass.  I asked my coworkers here to see if they have any idea.  They suggested the use of Office 2010 Access Database Engine.  This was suppose to be the end all be all to my problem but it wasn’t.

No matter how I configured the connection manager for the Office 2010 Access Database Engine, The destination column is never more than 255 columns.  I have tried it on four different computers thinking that I might be missing something.  So I gave up for a few days.

My machine here at work has cozyRoc SSIS+ software installed so I used the Excel Plus task and it work SOOOO FLAWLESSLY.  I didn’t even need to have a data conversion transformation task setup!  The export was faster than I was able to blink; however, I didn’t want to rely on a 3rd party application.  There HAS to be a way to get this information without a 3rd party app.

After f-ing around on this a few more days, I was going to relent and use cozyRoc’s Excel Plus task but I refused to give up and wanted one more go.  I recall that I can use Csharp with the Script Task so I tried that.  I looked online and there was a lot of sites that talked about importing from Excel to SQL, which is the opposite of what I want.

After some looking and switching between Bing and Google and keywords, I found a few Csharp code that would work because a lot of them had an interface.  I don’t care about the interface and wanted a console app.  The one I ended up using was from this site.  I was able to take that bit of code, fix up some of the problems with it and made it into an executable.

If you were to take the code from that site and stick in into the Script Task, you will need to upgrade the project to .net 3.5 because by default, SSIS creates Csharp project as .net 2.0.  In addition, because the Csharp code take one argument, your Script Task will fail because you can’t past arguments into SSIS.  You will get an error that read something like this:

Error loading ‘Package.dtsx’ : The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails.

That’s why I adjusted the original code and remove stuff that didn’t work or were unused or unnecessary.  See attachment below for my version, which include the source code, executeable, and config file.  The config file will prevent you from having to recompile everytime you want to change the sql syntax, server, or filename. You’re welcome.

Ah yes, you might need Visual Studio 2010 or NotePad++ to look at the solution.