jump to navigation

TempDB shows available free space in negative March 28, 2013

Posted by msrviking in DB Administration, SQL Server 2008, Uncategorized.
Tags: , , , , , ,
add a comment

I was browsing through one of my favorite forums to replenish my knowledge on SQL Server based on others experiences. While I was doing that, I found this interesting question which made me think “how could that be possible?”.

I am not writing this post as a solution but wanted to share the forum post, and discussions that are happening by other experienced DBAs. Well if that is not enough a Gent has mentioned couple of links in the forum, on why this behavior could be there first in place. As expected, this was a bug in SQL Server 2008 SP1 and fixed in SQL Server 2012, but I am not sure if folks are having issues with SQL Server 2008 R2 though.

Here are those links that you might want to refer in case you have issues. One link explains the probable reason of the behavior, and the other one is a connect item.


I couldn’t and didn’t replicate the issue if at all I had to, because there is documented information on this. BTW, I haven’t seen any response on this question post by the person who had asked. So I am presuming this is the reason and probably could be fixed or is fixed.

Cheers and Thanks.


Ask the core team? February 18, 2010

Posted by msrviking in DB Administration.
add a comment

I was doing morning rituals (reading blogs of my blog roll) and got a reference from Grumpy Old DBA to this interesting blogging site. If you want to know how Windows work SQL Server and much more beyond that, then why not visit this.


Name That Caption Contest February 3, 2010

Posted by msrviking in DB Administration.
add a comment

Tom LaRock is conducting an interesting contest over here. My captions would be

“I am done with my work today,  here is what I want to do for tomorrow?”.

“How do I achieve 99.99% SLA with all these jokers around?”.

“What is that I should budget for this year?”.

“How do I fly between these tall buildings?”

Running Profiler on SQL Server 2000 May 18, 2009

Posted by msrviking in DB Administration.
add a comment

Sometime last week I was asked by one my of team guys, on how to run profiler on SQL Server 2000 without providing sysadmin rights to the login which would run the profiler. Well here are couple of thoughts I had shared with my teammate.

1. In SQL Server 2005 and SQL Server 2008 you could run the trace /profiler by providing explicit permissions to run the traces.  The syntax is as such

USE master

2. In SQL Server 2000 this isn’t possible and one has to give sysadmin permissions to the user that would run the profiler. Unfortunately, this has implications on security issues on any environment because of the kind of rights that are given. So, I tried recollecting on one of the ways I tried few years ago. Here is what I responded back (with all due respects to the original idea I had borrowed from),

– Create a new user with strong password and give  sysadmin rights

– Run the profiler from a batch file with the command profiler /Sserver /Uuser /Ppassword /Tx

– The batch file had the user id and the password that was created for the profiler operations

– Converted the batch file into an executable so that the password is not exposed

To get the details of the parameters that could be included with the profiler, in the command prompt use Profiler /? and it would give you bunch of options.

I hope this type of stuff would help you guys too!

SQL Server 2005 SP3 is here! December 16, 2008

Posted by msrviking in DB Administration, SQL Server (SP's).
add a comment

Alas! the one we are waiting for all this while is out for download. This service pack SQL Server 2005 SP3 has consolidation of all the CU packages and fixes.

What has improved, quoted from “What’s New in SQL Server 2005 SP3” is on DB Engine, Notification services, Replication and Reporting services.

Check this link on what has improved, and download the SP3 from here.

BTW, please don’t apply the service packs on the production or any application critical environments. Try it on some sandbox to watch the behavior, test it and deploy it in appropriate environments.


Yipee, time for testing again! November 3, 2008

Posted by msrviking in SQL Server (SP's).
add a comment

SQL Server 2005 SP3 CTP version is out. You will want to download  a copy for yourself here.

However, let me caution you guys don’t try this on your production boxes. Ideally, you will want to download, run it against a sandbox environment, test the stuff you are looking for, (and test, and test), get all the stakeholders in your shop understand on the changes that would be available in the RTM and then plan for deployment in production (of course again after testing)!. Somehow, I love this word “testing”.

To quickly quench your curiousity here are the areas which is addressed in this service pack – Database Engine, Notification Services, Replication and Reporting services. Try it for yourself, and let me also know what did you find.

Happy reading and testing.

Why is shrinkdatabase not releasing space to the drive? May 7, 2008

Posted by msrviking in DB Administration.
add a comment

Back again with something very peculiar. My day started off
with one one of my fellow mates coming up to me.

Here is the conversation

My Buddy: "There is a 19GB table sitting out in one of the
environments instance, and I want to get rid of it."
Me: "Why do you want that table to out of the database, something
wrong?" (I was having several questions, but kept myself drawn into limits
so that I don’t bug him with my questions.)

My Buddy: "I need to release space to one of the drives on which
the database is sitting? Can I shrink only that table after deleting or
truncating the data?"
Me: "No, you can’t shrink a table to reclaim space for the
drive as long as you have other objects sitting in the same filegroup. You
could shrink the table alone if you have only that table in the
filegroup. The other way to reclaim the space could be by dropping the table
and shrinking the files of the filegroup."

Alright, now what! The database physical structure is that there are other
objects along with this 19GB table. So, my buddy went ahead and dropped the
table, and started shrinking the database to reclaim the space. Surprise, it
didn’t work. The database didn’t give back the space to the drive. After
several attempts, some service restarts, and txn log backups my buddy got back
to me with the issue that "It is not happening". I took few minutes
to think thro’ the sequence of shrinking I would do and sent another salvo of
questions "What are the commands you have run? What time did you run? Did
you see any errors after shrinking? What is the current size of the database
and database files? Were there anyone else on the instance during this
maintenance work…” (I am restricting the questions, so that you guys don’t
get psyched of the kind of naive questions I keep asking all the time, to everyoneWink). 
Well here are the answers in the order of questions asked "dbcc
shrinkdatabase(databasename, truncateonly), evening (5 PM – 8 PM), No, ‘x GB –
DB, used space – 45 GB, free space – 28 GB (after dropping the table) – Data
file, used space – 3 GB, free space – 300 MB- txn log’, No".

After I had my fill, I went ahead running this command

and bingo, I have had my best shot. The drive space had gained 18.5 GB space
which didn’t happen thro’ dbcc shrinkdatabase. My buddy was elated and asked me
how did I do it? I just had run this command and it worked out, and I was asked
why dbcc shrinkdatabase didn’t work. OK, now it is turn of others to fire
salvo’s at meCrying.

I did little search on my blog roll and found an interesting article by
PSS team member (Bob Dorr – SQL Server Senior Escalation Engineer) – http://blogs.msdn.com/psssql/archive/2008/03/24/how-it-works-dbcc-shrinkdatabase-shrinkfile-tabular.aspx

Though this article was on a different issue but it revealed few things for me
(an insight) and here is quick stuff I learnt and thought of sharing with you

  • The shrinkdatabase command is based on a percentage
    where as shrinkfile is based on a target.  
  • Shrinkdatabase uses the percentage to calculate a
    target for each file but can’t override the minimum file size.
  • ShrinkFile allows shrinking below the minimum file
  • dbcc shrinkdatabase(‘db name’, tabular) gives you a
    report of a dbcc shrinkdatabase without invoking the shrink actions.
    (report only)

WARNING: As always any undocumented syntax can
change and may not be supported.  This should be used under the guidance
of Microsoft SQL Server Support.

The shrinkdatabase command results
in a loop over each of the files in the database.  Using the current free
space of the file and the percentage the shrink target is calculated for each
file.   If the calculated target is less than the minimum size of the
file the error is produced and the shrink attempt aborted for that file.

Based on the last bunch of lines from the PSS blog, I understand that when dbcc
shrinkdatabase was run the file couldn’t shrink because calculated target was
less than the minimum size of the file
(but returned no errors, and this is
a surprise). I am leaving "why no errors didn’t come up?" to other
experts to explain or maybe I shall discover myself someday.

I hope you guys have had a good reading. Let me know your thoughts!