:::: MENU ::::

Posts Categorized / dbDigger

  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Increase or shrink file size for SQL Server database files

Following handy scripts help to manage the SQL Server database files. You may increase the max size and current size as well.

-- Increase file max size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', maxsIZE = 1500MB)  
 GO  
 -- Increase file size  
 USE [master];  
 ALTER DATABASE DatabaseNameHere MODIFY FILE ( NAME = N'FileNameHere', sIZE = 1500MB)  
 GO  

Shrink operation may be up to a specific extent or just shrink by removing the space available ay the end of file without manipulating data in file.If file shrink operation is cancelled during execution the current status is not rolled back. TRUNCATEONLY operation is fast.

-- Shrink to specific limit  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 200)  
 GO  
 -- Shrink to space available at the end of file  
 USE DatabaseNameHere;  
 DBCC SHRINKFILE (N'FileNameHere' , 0, TRUNCATEONLY)  
 GO  
  • Oct 01 / 2014
  • 0
dbDigger, T-SQL Scripts, T-SQL Tips and Tricks

Get all file groups and file details in a SQL Server database

Following script will provide information about the filegroups and files in a given database. It includes names, location, allocated size, used size and percent free.

-- get data file space and locations  
 Use DatabaseNameHere;  
 SELECT b.groupname AS 'File Group'  
   ,a.NAME  
   ,physical_name  
   ,CONVERT(INT, a.Size / 128.000, 2) AS [Currently Allocated Space (MB)]  
   ,CONVERT(INT, FILEPROPERTY(a.NAME, 'SpaceUsed') / 128.000, 2) AS [Space Used (MB)]  
   ,CONVERT(INT, a.max_Size / 128.000, 2) [Maximum Space (MB)]  
   ,CASE   
     WHEN a.IS_PERCENT_GROWTH = 0  
       THEN CONVERT(VARCHAR, CONVERT(DECIMAL(15, 2), ROUND(a.growth / 128.000, 2))) + ' MB'  
     ELSE CONVERT(VARCHAR, a.growth) + ' PERCENT'  
     END [Growth]  
   ,CONVERT(INT, (a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2) AS [Available Space (MB)]  
   ,(CONVERT(INT, ROUND((a.Size - FILEPROPERTY(a.NAME, 'SpaceUsed')) / 128.000, 2)) * 100) / (CONVERT(INT, ROUND(a.Size / 128.000, 2))) AS PercentFree  
 FROM sys.database_files a(NOLOCK)  
 LEFT OUTER JOIN sysfilegroups b(NOLOCK) ON a.data_space_id = b.groupid  
 ORDER BY PercentFree  
  • Aug 26 / 2014
  • 0
Data Modeling and Database Design, dbDigger, SQL Server Collations

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Today one of our development team member reported following error on newly restored database in staging environment.

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Error was generated in a stored procedure. First of all i found that collation of subject database is different than SQL_Latin1_General_CP1_CI_AS. Using this method i changed the collation of subject database to required SQL_Latin1_General_CP1_CI_AS.
I was hopeful that changing collation of database will solve the issue but it was not the case. I opened the stored procedure code and got the tables being used in it.
Suppose we have table1, table2 and table3 in USP. Now we have to get all those columns where collation is different than our required collation. Following script did task and returned the columns with different collation.

SELECT object_name(object_id) as TableName,   
 name as columnName, collation_name  
 FROM sys.columns  
 WHERE OBJECT_ID IN (SELECT OBJECT_ID  
 FROM sys.objects where name in ('table1','table2','table3'))  
 and collation_name <> 'SQL_Latin1_General_CP1_CI_AS'
GO

Once we have the list of columns then we can change their collation to match with required collation. This can be done with following script.

ALTER TABLE table1 ALTER COLUMN ColumnNameHere nvarchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table2 ALTER COLUMN ColumnNameHere varchar(64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
GO
ALTER TABLE table3 ALTER COLUMN ColumnNameHere varchar(512) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
GO

This time i found that issue was resolved and there was no error.

  • May 06 / 2014
  • 0
CLR, dbDigger, High Availability, SQL Server Clustering, SQL Server logs

Using ‘odsole70.dll’ version ‘2009.100.1600’ to execute extended stored procedure ‘sp_OACreate’

Recently we came across unexpected cluster fail over of one of our servers. It was required to get the exact reason for it. I analyzed the sql server and windows logs. There was no traces of failure neither i found any major issue in the logs that may lead to fail over. However an entry in the logs caught my attention and it was following message

Message
Using ‘odsole70.dll’ version ‘2009.100.1600’ to execute extended stored procedure ‘sp_OACreate’. This is an informational message only; no user action is required.

I googled it and came to know that it is culprit of event. According to its BOL page

You call some Automation procedures from a SQL Server common language runtime (CLR) object, such as sp_OACreate. In this situation, SQL Server may unexpectedly crash.

Note This issue also occurs when a CLR object calls a Transact-SQL procedure that calls Automation procedures.
It applies to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2012. More detail can be found here.
Now we have to trace the call and modify it to avoid the accidental fail over.

  • Mar 24 / 2014
  • 2
dbDigger, Replication, SQL Server Error messages

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint (Replication error)

We had configured transnational replication on a QA server. Publisher and subscriber were on same server. While implementing snapshot we were facing following error message as in replication monitor.

Command attempted:
if object_id(N’sys.sp_MSrestoresavedforeignkeys’, ‘P’) < 0 exec sys.sp_MSrestoresavedforeignkeys
(Transaction sequence number: 0x000B0F2B0002BB5101C300000000, Command ID: 1)

Error messages:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint “FK_ContractID2”. The conflict occurred in database “%”, table “%”, column ‘%’. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547

Subscriber database had no constraint that was mentioned in the error message. Even publisher did not have this constraint. It was a mess that we were not able to find the constraint but it was causing failure to snapshot implementation.

Towards Solution

While looking into details i came across following there tables that were being created by replication processes and contain the foreign key data of replication.

  • dbo.MSsavedforeignkeys
  • dbo.MSsavedforeignkeycolumns
  • dbo.MSsavedforeignkeyextendedproperties

I noticed that ghost enteries for our culprit foreign key were there in these tables. I deleted those entries manually and regenerated the snapshot. This time it was successfully generated and implemented.

  • Jan 31 / 2014
  • 0
Database Mail, dbDigger, SQL Server Agent scheduled Jobs, SQL Server Error messages

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Last day i was working on a task which was meant to generate some data and send it as an email attachment to a specific email address. This process was going to be configured as a scheduled job in SQL Server agent. I completed the first part of task and data was ready as a CSV file to be send through email.

Problem Phase

In the second phase while trying to send the file as an attachment i got following error message.

dbmail error File attachment or query results size exceeds allowable value of 1000000 bytes

Amount of bytes shows that about 1 MB file size cap was there on attachment file that should be send through DBMail. In my case we were expecting the attachment file of size up to 25 MB.
So the questions are

  • Can we change the default limit on attachment size in database mail .i.e. 1000000 bytes?\
  • If yes then where can we change this parameter?

Solution

Yes we can change the default limit on attachment size in database mail. And related to the procedure of changing this parameter following are the steps

  • Open SSMS and go to Management folder
  • Right click on Database Mail and select Configure Database Mail
  • Click Next and select view or change system parameters radio button
  • Click next and you will be presented the parameters screen where you can view and modify the parameters as well
  • Here you can change the value against the Maximum File Size (Bytes) parameter
  • Specify the required size in bytes and click Next to save the configurations

Now you can send attachment up to mentioned max size here without any error.

What is the limit of Max value?

It is a valid question here at this point that what is the limit of value that we can enter as maximum file size in bytes? The answer is 2147483646.
Yes this parameter is of type int and can accept value under 2^31-1 (2,147,483,647) bytes. This mean that roughly you can get about 2GB size against this parameter. If you enter exceeded value for size then following error message will be generated

Account Retry Attempts, Account Retry Delay, Maximum File Size, Database Mail Executable Minimum Lifetime, and Maximum Number of Mails must have valid values to continue.

Value was either too large or too small for an Int32. (mscorlib)

Pages:1234567...84
Consult us to explore the Databases. Contact us