Space issue of SQL Server Database how to claim free space of SQL SERVER ?

Dear Friends,

I have faced recently database size issue. In my current project I need to add a column bit field in table and update the bit field conditionally.

The table contains 547,173,777 rows. Before adding the column the database size in 177 GB and after adding and updating the Bit column for the table the database size increased 223 GB.

I am suppressed with that behavior.

So my main motto at that time first recovers space. I searched many articles for claim space. Firstly I am sharing the with you all the options related to space and later on I will share the steps which help me to recover space

1) Shrink File:-
Shrink file is the most used option to recover space again from database it mainly used to recover space from log files.

Its syntax is DBCC SHRINKFILE (Database_LogFileName, Target Size MB)

2) Shrink Database:-
Shrink Database is another option which is used to shrink Database. With this option we physically gain the size.

It syntax is DBCC SHRINKDATABASE (DatabaseName, Percentage)

3) Clean Table :-

Clean table option can be used in particular condition suppose you have added a VARCHAR column in table and added data also to that column now later on you want to remove the column then After removing the column you will see the table size is unchanged to recover VARCHAR column space you can use CLEANTABLE option.

Its syntax is DBCC CLEANTABLE (DatabaseName, TableName)

4) SP_SPACEUSED:

This option will provide you space used by table or database .sp space used column will return number of rows, reserved space, Data space, index space, unused.

Its syntax is SP_SPACEUSED ‘TableName’
5) UPDATEUSAGE:-

Update usage is another most important command which update accurate Rows & pages count. Its syntax is as follows DBCC UPDATEUSAGE (DATabaseName, tableName)

6) Index Defrag:
Index Defrag is one of the main commands. It should be used in maintenance plan also this command defrag the entire index and rearrange the index.

Its syntax is as follows DBCC INDEXDEFRAG (DatabaseName, TableName, IndexName)
Other than this re indexing, re organizing index are option which help in actual database size projection. So all the above statements are related to the space information for database.

Now in my case I got help from INDEXDEFRAG command.

I did defragmentation of all the index of a table on which I have added the bit column. After running defragmentation for each index I was amazed when I run SP_SPACEUSED it recovered almost 40 GB. But the problem not ends here when I saw my disk drive it not reflect actually on disk. Then I used shrink Database command which increase my server disk free space with 40%.

So here are the steps to recover space from a table or database

1) Check current space used by table with SP_SPACEUSED command
2) Run Index defragment command INDEXDEFRAG for each index on table
3) Shrink the Log file with SHRINKFILE command

4) Shrink the database using SHRINKDATABASE command

After running above steps you will surprised. I hope these steps will help you.


Thanks & Regards

Rajat Jaiswal

Create Database with isqlW command

Hello friends,
Batch files many times reduce our work. Many tables, stored procedures, functions times we need to create same database, tables, stored procedures, and pre pop data on multiple instances or even same environment. Then in that case we just create scripts and run again and again in SQL Management Studio. But if you are in hurry and you want to run the script on single click without opening file then the easiest way is use ” ISQLW” .isqlw is the another way and we can use it with batch file.
So here are the basic steps:-
Suppose I want to create friends DB database with all theand pre pop data.
Then step 1:- Generate, a script file in which we will keep all the create table’s statements, create procedures statements and pre pop scripts.
Step2:- create a batch file for this just open notepad and save the file with extension “.bat” then the file is saved as batch file. (A batch file is executable file)
Step 3: Just write isqlw command in that as shown below
IsqlW -E -i "\fdb.sql" -o "\rajat.txt"
Where -I switch for input file – o for output file. With isqlw you can give credential also but my sql server is with mix mode so there is no problem.
After writing isqlw statement just save it and run it will create database FriendDB with all the tables, stored procedures and prepop data.
And the entire log will register in rajat.txt file.
In this way you can enjoy one click database creation with all tables’ stored procedures and pre pop data.

Thanks & Esteemed Regards
Rajat Jaiswal