From the category archives:

DBA

How to find a Column Name in Database

March 2, 2011

in DBA, SQL Basics

There are many instances when we want to get a list of all the columns in a database (or) find a particular column in a database, not sure in which table it is!!! OR the case may be even we want to find a column whose prefix or suffix may be know to us.

Instead of manually searching for column, we can query the sql server system tables and find that information.

Here is the query, you can just run it and see the results by your self. Actually it is selecting data from three system catalogs; Schemas, Objects and Columns. You can also try out selecting data from them individually and see what they return, these are really useful.Here, in the where clause I have put condition to get data for User Tables (U) and Views (V).

SELECT S.name as SchemaName,O.name as ObjectName, O.type_desc as ObjectType,C.name
FROM sys.schemas S
INNER JOIN sys.objects O ON S.schema_id=O.schema_id
INNER JOIN sys.columns C ON O.object_id=C.object_id
WHERE O.type in('U','V')
ORDER BY S.name,O.type_desc, O.name,C.Name

And here is how you may be seeing the result.

SQL Server Find Column Name in Database

{ Comments on this entry are closed }

SQL Server Database Restore from SQL Server 2008 R2 to 2008

December 19, 2010

in DBA

Recently I was working on a database which was on two different versions, SQL Server 2008 and SQL Server 2008 R2.

And in the middle I wanted to do a restore from SQL Server 2008 R2 to SQL Server 2008, and went through a bit of issues, and finally found out that there is no easy way to get out of this Issue.

I had taken a backup of the SQL Server Database from R2 and tried restoring it on SQL Server 2008, and got the below message,

“The database was backed up on a server running version 10.50.1600. That version is incompatible with this server, which is running version 10.00.2531. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server. (Microsoft.SqlServer.Smo)”

When we look through the above error, that’s clear that, the basic sql server physical version is different for both SQL Server SQL Server 2008 and SQL Server 2008 R2.

SQL Server 2008 R2 is 10.5

SQL Server 2008 is 10.

So, now the issue was on how to accomplish it, the below is what had to be done, it is laborious manual way, I suppose it is only the developers that encounter such problems, so Microsoft will not for sure address it J  because even if the feature is there, there would be many few people using it.

We need to generate a script of the complete database with a proper Script Server Version selected.  Can be done by

Tasks –>  Generate Scripts–> Script entire database and all database objects  –> Save the File –> Got to Advanced –> Select proper Script for Server Version 200/2005/2008  –> Type of data to Script –> select Schema/Data/both  –> Execute it on the target server.

Of course you will be getting lots of errors w.r.t the primary-foreign key relationships, need to address them all, or do a manually by using the import and export wizard w.r.t the data.

Please let me know if there are any other suitable better performing methods for accomplishing the above in comments.

Tagged as: Backup, restore

{ Comments on this entry are closed }

How to Drop a SQL Server Database

December 29, 2009

in DBA, General, SQL Basics

 Running a DROP statement on an SQL Server is never an easy task as far as the numbers of thoughts that run in our mind at the time of doing that are considered.

So, here is writing up on the process that we need to follow while dropping a SQL Server Database.
Firstly, the decision process that you have to really go ahead and drop the database, if yes then create a checklist of your own with the below as a starting point to it.
1.       Check if any application is using the database,the chance is that they need to be pointed to a new upgraded database or the application is obsolete now and simply needs to be removed.
2.       Check if any other databases are using objects of this database.
3.       Check if you have any Database Level Triggers on this particular database.
4.       Check if you have any Maintenance plans, exclusively referring this database.
5.       Check if you have any MS SQL Server Agent Jobs referring this database exclusively.
6.       Check if you have any Database Snapshots, if yes, Drop the database snapshots on the database.
7.       Check if you have log shipping configured, If the database is involved in log shipping, remove log shipping before dropping the database.
8.       Get a thumbs up from the owner of the database
9.       Check if there are any existing users. If yes, notify them, or if they are long running check what operation they are trying to do and kill them.
10.   Preferably, get the database to SINGLE_USER mode.
11.   Take a full backup with “verify the backup” option on, rather take multiple copies with at least one being offsite.
12. Create a script of all the Logins in that database, in case you may miss the logins at the place of Restoration.
13.   Take a backup of the master database.
14.   Drop the database.
15.   Take a backup of the master database.
 
Now for some basic points
Syntax: DROP DATABASE Database Name
For deleting a database you need to at least have CONTROL permission on the database.
We can delete a database regardless of its state – Offline, Read-only or Suspect or any other.
 
If you have liked the article and want to get the articles (or) new posts at sql like, subscribe to articles through email by entering your email address in the top right and verifying the same. if you have any comments, then the below form is for you, go ahead!!!

Tagged as: Database, Drop

{ Comments on this entry are closed }

Everything about Sizes in SQL Server

November 1, 2009

in DBA

There have always been many questions in the forums related to SQL Server, regarding the size of various objects in SQL Server and maintaining them, so, I have thought to put all the helpful information in the form of an article, and here it is.

 

How do I find the Size of a database?

 

You can always get it from the management studio, right click and then properties.

 

Suppose, if you want to do it through the query analyzer, to get the database size for all the databases then you can do it using

 

sp_databases

 

This system stored procedure would return you a list of databases and their sizes in KB, and the result for the same would be like the one given below,

 

DATABASE_NAME     DATABASE_SIZE           REMARKS

master                  4864              NULL

model                   1984              NULL

msdb                    19968             NULL

tempdb                  36360             NULL

 

If you want to get more details, like, the database id, compatibility level, created date and who is the owner in a single stroke you can make use of the system stored procedure

 

 

Sp_helpdb

 

 

the status column is not displayed as the result was too lengthy…

 

name  db_size     owner dbid  created status compatibility_level

master      4.75 MB     sa    1     Apr  8 2003       90

model       1.94 MB     sa    3     Apr  8 2003       100

msdb        19.50 MB    sa    4     Oct 14 2005       100

tempdb      47.82 MB    sa    2     May 27 2009       100

 

And there is one more system stored procedure which would be of great use for the system administrators who want to keep a watch on the used/unused, allocated/ unallocated and the size of indexes, it is

 

sp_spaceused

 

example reluts for the same are displayed below.

 

 

 

 

database_name     database_size     unallocated space

master            4.75 MB           1.04 MB

 

reserved    data  index_size  unused

3032 KB     1320 KB     1208 KB     504 KB

 

How do I find the size of Log file, and how much is used?

 

There is a standard DBCC Command particularly for this,

 

dbcc sqlperf(logspace)

 

Note: – The DBCC (Database Console Commands) Statements are provided in the transact SQL server programming language for checking physical and logical consistency and also for fixing most of the issues.

 

And the result of the same would be like, given below

 

Database Name     Log Size (MB)     Log Space Used (%)      Status

master            0.7421875         68.42105                0

tempdb            15.5              53.2006                 0

model             0.4921875         45.23809                0

msdb              0.7421875         63.15789                0

 

 

I want a list of all the files in a database with their sizes?

 

You can get the above information by running the below query in the desired database, this will return you a list of files, with their locations and size details.

 

Sp_helpfile

 

name  fileid      filename    filegroup   size  maxsize     growth      usage

master      1     C:\Program Files\DATA\master.mdf    PRIMARY     4096 KB     Unlimited   10%      data only

mastlog     2     C:\Program Files\DATA\mastlog.ldf   NULL  3136 KB     Unlimited   10%   log only

 

How do I find the Size of a table?

 

That’s bit simple; we need to just use the system stored procedure with a parameter of the table name for which we want to find the size sp_spaceused, Let me take an example here, suppose, there is a table in the msdb database with the name backupfile and I want to see the size statistics for that file, then, I would run the below code

 

use msdb

go

sp_spaceused backupfile

 

and the result for that would be

 

name        rows        reserved    data        index_size  unused

backupfile  314        328 KB      184 KB      16 KB       128 KB

 

 

How do I find free space in a drive on which I have my SQL Server running?

 

Most of the times, people cannot log into a server and they want to get the size details of the disk from query analyzer, for this there is an extended stored procedure provided directly, one thing that needs to be kept in mind is it gives the amount off free space left in the disk drive, and make sure that it never hits “ZERO”. And also it gives that numbers to us in MB…

 

Usage:-

 

 

Use Master

go

xp_fixeddrives

 

 

And the result for that would be some thing like below

 

drive MB free

C     8111

F     3154

 

 

Okay, that’s fine, till now we have see how to find the size of a database, size of a log file, size of a table and free space in the disk drives. Now let us take couple of issues that people face in general

 

Many people face these issues nearly on a daily/weekly basis…below are some answers for the same, there can be more methods off tackling the same issues

 

Temp DB is out of space, what should I do?

 

Depending on the server on which it is happening firstly, you have to tackle this by shrinking the log file, for this we have the DBCC SHRINKFILE Statement

 

For shrinking the log file, I can use a statement like this,

 

DBCC Shrinkfile(2,1) – where 2 is the file id and 1 is the amount of free space that is to be left.

 

If that doesn’t help you, then go and shrink the data files by using the statement

DBCC Shrinkfile(1,1)  — one by depending on the number of data files that you have.

 

Yes, even now if you are not able to fix it, then I think check the possibilities, and restart you SQL server services, that’s it issue resolved. But then, make sure that you place your Tempdb at a place where it has space to GROW!!!

 

 

My database Log file is full, what shall I do…..???

 

Same principle, first try and shrink the log file, most of the time it works out!!!

 

Worst case scenario; detach the database, by either go to all tasks or doing an

EXEC sp_detach_db ‘MyDB’

Then re attach the mdf file using

EXEC sp_attach_single_file_db @dbname = ‘MyDB’, @physname = N’C:\Program  Files\Microsoft SQL Server\ Data\MyDB_Data.mdf’;

 

 

If the database is not for a transactional system then we can keep the recovery mode of the database to Simple so that we don’t face these kind of issues as the log file is truncated ever time a COMMIT is done.

 

 

That’s it for now in this article, hope you have found some basic points, all at one place, and think it helps, please let me know what you have felt by voting this article.

Tagged as: DB Size, Disk Space, Table Size

{ Comments on this entry are closed }