Difference between Data file shrinking and Log file shrinking

Posted: October 29, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , ,
0

You may be confused to find this question’s answer, after so lots of google, you may read that shrinking data files is good, and in other places you may read that it’s bad.

A shrink operation on a data file tries to move the database page nearest the end of the file down toward the start of the file. This creates “empty” space at the end of the data file that can be returned to the OS. In other words, the data file is made physically smaller.

A shrink operation on a transaction log file, on the other hand, doesn’t move anything around—it simply removes the empty portion of the transaction log at the end of the file, as long as the transaction log records are not being retained for any reason. If the operation is successful, the log file is made physically smaller.

The confusion comes from the side effects of the two operations, and when they should be performed.

People are advised (or decide) to shrink data files to reclaim space. It may be that their index maintenance job causes the data files to grow, or their drive is getting full, and the natural reaction is to reclaim some of this “wasted” space. However, it is likely this space will be needed again and often it’s better to leave the remaining free space available to the data file rather than repeatedly shrink and auto-grow a file.

Shrinking a data file should be a very rare operation because it has a nasty side effect. Shrinking a data file causes massive index fragmentation, which may affect query performance. For same you may read Paul S. Randal blog post “Why You Should Not Shrink Your Data Files” includes a simple script that shows this.

Deleting files older than n days using command FORFILES

Posted: October 29, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , ,
2

It is always very challenging and energetic know some command line fun magically, as it is sometimes very easier and powerful tips to process commands that way. Here, I would like to say something this type of funny operations with Forfiles command in Windows Server 2003/ Windows Vista /Windows 7/ Windows 8 or higher. Forfiles can process files based on names, file extensions and file life cycle. It’s very easy to find files depending on our search criteria or all files in a directory/or including sub directories that are older than Nth days, or all documents in specific folder that have been changed since a specific date.

The forfiles command can be used with processing options to delete those files, or create a list of all files that match the search criteria.

Forfiles, Selects and executes a command on a file or set of files. This command enables batch processing of files. In conjunction with a SQL Server Agent job, we can easily use this command to delete old database backup files to remove dependencies on items such as SQL Server maintenance plans, the xp_cmdshell extended stored procedure, or VBScript objects. For example, the following command searches the directory c:\test and all its subdirectories for files with the .BAK extension that are older than 10 days

FORFILES /P C:\TEST\ /M .BAK /S /D -10

The parameter /p followed by a directory defines the starting directory, /s includes subdirectories in the search, /m filters the files and folders based on the search criteria, and /D defines the date or a time span.

For more details visit http://technet.microsoft.com/en-us/library/cc753551(v=ws.10).aspx#BKMK_examples

Find the number of CPU’s through T-SQL query

Posted: October 26, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , ,
0

One day I have to find how many numbers of CPU are working on my DB Server, before know below options, I always take a Remote Desktop session for server and from Task Manager let know about CPU information and many more available information provided by Task Manager.

Option :1
    Using extended stored procedures XP_MSVER , there at index 16, processor count displays

Exec
xp_msver
‘processorcount’

or we can directly use processorcount
Exec
xp_msver
‘processorcount’


Option :2
    Using ‘Set NUMBER_OF_PROCESSORS’ which run on command prompt.
Exec
xp_cmdshell
‘Set NUMBER_OF_PROCESSORS’


Option 3:
From Registry values using extended stored procedure XP_REGREAD

Declare
@key
varchar(1000)

Declare
@value
varchar(1000)

EXEC
master..xp_regread
@rootkey=‘HKEY_LOCAL_MACHINE’,@key=‘SYSTEM\CurrentControlSet\Control\Session Manager\Environment\’,@value=NUMBER_OF_PROCESSORS

Identify blocking in SQL Server

Posted: October 26, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , ,
0

In all version of SQL Server we face Locking and Blocking problems. Now its very first thing to know blocking and blocked SPID. When a user or DBA itself come to know that particular process seems to hung or a process is not processing properly, only checking the applicable database blocking makes a great deal to winning the battle. Blocking happens when one connection from an application holds a lock and a second connection requires a conflicting lock. These forces the second connection to be blocked until the first connection completes. With this being said, locking is a natural occurrence in SQL Server in order to maintain data integrity.

In SQL Server, there are many ways to findout the blocking and blocked SPID/Process. Some options are like:

  • sp_who2    System Stored Procedure
  • sys.dm_exec_requests  DMV
  • Sys.dm_os_waiting_tasks   DMV
  • SQL Server Management Studio Activity Monitor
  • SQL Server Management Studio Reports
  • SQL Server Profiler

Find running Queries completion time

Posted: October 26, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , ,
2

For DBA day-to-day activities, Sometimes it happened, anyone executed queries on production server and after long waiting, he/she come to DBA and ask a very funny question like I have executed this/that queries and its running since long time, now tell me how
much time it will take to complete

Sometime its very difficult to explain but from using sys.dm_exec_requests DMV, somehow we can find out the answer like

SELECT percent_complete,start_time,status,command,estimated_completion_time/1000/60 As ‘Minutes to Completion’,
total_elapsed_time/1000/60 As ‘Minutes Elapsed’, wait_type, last_wait_type FROM sys.dm_exec_requests
order by ‘Minutes to Completion’ desc

Compare the extra Columns between Tables

Posted: October 25, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , , ,
0

Sometimes its needed to check where two tables having the same number of columns?

To demonstrate this first create two tables as EMP1 and EMP2.

Lets we have two Tables as EMP1 and EMP2 as below,

Suppose table EMP1 is as below

USE [VirendraTest]

GO

CREATE TABLE [dbo].[Emp1]
( [ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](10) NULL,
[Basic] [numeric](18, 2) NULL
   ) ON [PRIMARY]

GO

and table EMP2 is as below,

USE [VirendraTest]

GO

CREATE TABLE [dbo].[Emp2]
( [ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](10) NULL,
[Basic] [numeric](18, 2) NULL,
[Age] [int]
   ) ON [PRIMARY]
 GO

Now to find the extra columns from EMP2 to EMP1 , we can use below query,

Select c2.table_name,c2.COLUMN_NAME from [INFORMATION_SCHEMA].[COLUMNS] c2
where table_name=‘Emp2’ and c2.COLUMN_NAME not in (select column_name from [INFORMATION_SCHEMA].[COLUMNS] where table_name=’emp1′)

Compare Data types between two tables

Posted: October 25, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , ,
2

Its required in our daily practices or sometimes we have to compare two or more tables to find out where tables are same in terms of column’s data types.

Below is the query to compare data types between two tables, for this, we can use the [INFORMATION_SCHEMA].[COLUMNS] system views to verify and compare the information.

Lets we have two Tables as EMP1 and EMP2 as below,

Suppose table EMP1 is as below

USE
[VirendraTest]

GO

CREATE TABLE[dbo].[Emp1]

(  [ID][int]IDENTITY(1,1)NOT NULL,
   [Name][nchar](10)NULL,
   [Basic][numeric](18, 2)NULL
) ON[PRIMARY]
GO

and table EMP2 is as below,

USE
[VirendraTest]

GO

CREATE TABLE[dbo].[Emp2]
( [ID][int]IDENTITY(1,1)NOTNULL,
  [Name][varchar](10)NULL,
  [Basic][numeric](18, 2)NULL,
  [Age] [int]
) ON [PRIMARY]
GO

Now to find the data type mismatched columns, use below query,

Select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c2.table_name,c2.DATA_TYPE,c2.COLUMN_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c1
Left join [INFORMATION_SCHEMA].[COLUMNS] c2 on c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME=’emp1′ and c2.TABLE_NAME=’emp2′ and c1.data_type<>c2.DATA_TYPE

Database Attach Error 5120

Posted: October 16, 2012 by Virendra Yaduvanshi in Database Administrator
0

I got this error when I was working with my one DBA, after so lots of basic finding, I came to know that this error occurred when anyone try to attach a Database (Physical MDF/LDF files) from any folder or copy from elsewhere, was previously attached with different SQL Instance (or same instance but previously startup account was different) and that instance Startup account (SQL Server Service) is not as your current startup account.

To resolve this issue you have to provide full access of physical files containing folder/files to that SQL Server startup account. After providing full access of that specific service account for folders/files, now you are able to attach that DB files.

Maintenance plan delete error 547

Posted: October 16, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , ,
0

Its happen, sometime we delete maintenance plan from SSMS, due to some reason it show succeed or error and in between that some object/steps being deleted but some not, sometimes you see maintenance plan job name in Job Activity Monitor/Jobs Listing and when you tr to delete that job, its through error like

Drop failed for Job ‘XXXXXXXXXXXXX’. (Microsoft.SqlServer.Smo)The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id(xxx)”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’.The statement has been terminated. (Microsoft SQL Server, Error: 547)

We can resolve this error as

  1. Find which plans needs to delete by :
    SELECT * FROM sysmaintplan_plans
  2. Using above query, you can get plane ID of your maintenance plan which you want to delete
    DELETE FROM sysmaintplan_log WHERE plan_id = ‘Plan ID of your needs to be delete Maintenance Plan’
    DELETE FROM sysmaintplan_subplans WHERE plan_id = ‘Plan ID of your needs to be delete Maintenance Plan’ 
    DELETE FROM sysmaintplan_plans WHERE id = ‘Plan ID of your needs to be delete Maintenance Plan’

    Just Delete entries from above mentioned these 3 tables and be Happy from such problem!

Database Owner Name

Posted: October 15, 2012 by Virendra Yaduvanshi in Database Administrator
Tags: , , , , ,
2

Its happened sometimes developer or DBA needs to know Database owner,

Here are some basic tips to find out Database owner,

Select  Name as ‘Database Name’,suser_sname(owner_sid) ‘Database Owner’ from sys.databases

– OR – –
Select  Name as ‘Database Name’,suser_sname(sid) ‘Database Owner’ from sysdatabases

Note : suser_sname returns name associated with a security identification number (SID).

Older Entries
Newer Entries