SQL: Cannot shrink log file 2 () because all logical log files are in use.
Written by: Mr.ML
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.
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 trie...
Extended Events, finally!
Written by: Mr.ML
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 gene...
SQL: Find out how big the differentials is going to be
Written by: Mr.ML
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
SSRS 2008 R2: Recovering a report from a database backup
Written by: Mr.ML
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 we...
SQL: no remote user ‘link_server’ mapped to local user ‘(null)’ from the remote server
Written by: Mr.ML
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...
Steve Wozniak Quote to remember
Written by: Mr.ML
“Make good things when you are young and you’ve covered your needs for life.” – Steve Wozniak.
SQL: fixing long running queries
Written by: Mr.ML
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 indexe...
C#: Could not load file or assembly
Written by: Mr.ML
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 ca...
SSIS: Data import lesson learnt
Written by: Mr.ML
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 ...
SSIS: Export more than 255 columns from SQL table to Excel
Written by: Mr.ML
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 so...