I faced an issue during restoration of very large db in our development database server. Issue is restoration takes very long time even though we had latest hardware and recent version of SQL Server (it’s SQL 2008 Ent. Ed). So to troubleshoot it I have used the trace flag 3004 which print all internal things happen during backup and restoration operation.
In this article I will try and cover the concepts in understanding the amount of memory or buffers required for completing a database backup. I would be using a couple of trace flags to get that detail and further discuss two of the parameters MAXTRANSFERSIZE and BUFFERCOUNT in the BACKUP DATABASE TSQL syntax which will help us understand a bit more about memory consumption for a database backup and time involved in completing the backup.
Does the title sounds crazy? You might think that this topic is very simple? Yes you are right however some folks still raise this question in forums. Im seeing this question for for a long time in forums and thought of sharing the solution here, so that the folks can give a search to get this (coz Google is our first techie teacher or instant solution provider 
).
You might be wondering what is happening in your SQL Server when you see the error message given below. Its all simple, you need not panic.
Introduction
Backup compression is one of the feature expected by every administrator to minimize the backup size and to squeeze the time taken for backup. In previous versions of SQL Server most of us are going for 3rd party tools like Quests Litespeed, Redgates SQL Backup etc. for which we need to pay additional amount. SQL Server 2008 adds an additional feature of taking compressed backup through native backup command itself.
RA
| #
Thanks for the script. I had database with space between the names.
I modified to use square brackets:
FROM [?].sys.database_principals a
LEFT OUTER JOIN [?].sys.database_role_members
and
from [?].sys.database_permissions join [?].sys.sysusers U
on grantee_principal_id = uid join [?].sys.sysobjects
Gangadhar NG
| #
This tip is helpful enough for me.
thanks.
Zukunftsmusik
| #
Thanks! That helped my a lot. But there’s a little mistake:
SQL_Latin1_General_CP1_CI_AS stands for “Latin1-General, case-insensitive, accent-sensitive [...]” therefore sort order 52.
If you want to set the sort order to 54 the correct collation would be “SQL_Latin1_General_CP1_CI_AI”
Amit Bhatt
| #
Hi Deepak,
Thanks for such a nice article.
You missed one thing to add in code:
@article = ‘all’,
Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’
Error 18486 | Platformblog
| #
[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]