In one of my project I was working on SQL Server 2000 to SQL Server 2008 upgradation. It’s a side by side upgrade so I need to restore the database from SQL Server 2000 to SQL Server 2008, in which some of the databases are readonly. I know I will be able to restore readonly database however in this case when I tried to restore the database in SQL Server 2008 I got the error below
AWE (Addressing Windows Extension) is one of the know server configuration to everyone. This parameter allows SQL Server to access large amount of memory in 32 bit machines. However MS decided to depreciate this configuration in SQL Server 2012 (Denali) . I hope this is the first step towards depreciating all 32 bit SQL server editions. So if you are using this setting in your current environment then you need to be careful while upgrading it to SQL Server 2012.
Denali CTP3 is already released and we have new DMV’s accompanied with this version which will be very useful for us. I’m going to cover the DMV that are all generic and not to a specific feature of Denali, I’ll will cover those DMV’s while writing article on that feature. There are around 8 DMV’s which I’m going to discuss it now.
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.
DBCC CHECKDB & READ ONLY DATABASES:
Can you run a checkdb command on Read-only databases? If yes, does this get logged into boot page of the database? If checkdb finds an error can it fix on a read-only database?
These were some of the questions running through my mind as I manage some read-only databases in an SQL server which is mostly used for archiving and wanted to have the same level of checks and monitoring stuff we do an read write OLTP databases.
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. [...]