I Want to test for Automatic switch between primary and secondary.
How do i do it ?
or
I need this for some purpose : How to set suspect mode for a database ?
Our development team wanted to create a database user for each application user in the application and use these for granular data access control, which at first, sounded like a good idea but our initial testing ran into some interesting results.
Our target user base was about 15 million users with an estimated 1% concurrency rate, and finding no MS documentation on an upper limit to the number of users a database can have we began some load testing to see how the database performed. In the hundreds of thousands of users range our test database had a hard time performing well under light loads (even without any concurrent connections).
When we purged the users and reverted back to just a handful of service accounts, performance went back to "normal" under the same loads. I began to wonder if this is a situation where throwing more hardware at the problem would overcome the issue or if there is a practical upper limit to the number of users a single database can handle well.
(There were of course other cons to this arrangement and I certainly was never going to expand the users tree in the object explorer for a database like this, but we thought it a solution worth investigating.)
What is the largest number of users any of you have had in a single database?
My database went into suspected mode. and after we had run some script, it came out from the suspected mode. but we encountered this error while opening table in database.
2009-11-02 15:46:42.90 spid51 Error: 824, Severity: 24, State: 2.
2009-11-02 15:46:42.90 spid51 SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:43686; actual 0:0). It occurred during a read of page (1:43686) in database ID 23 at offset 0x0000001554c000 in file 'H:MSSQL.SQL2008MSSQLDATAmy_db.mdf'.
Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
I have a database that I am trying to recover space from, it consists mostly of unallocated space, but I can’t seem to get that unused space released.
Database size: 40,245.13 MB
DatafileMB: 38,063.63, DataAvailableMB: 37,085.15
LogfileMB: 2181.51
Sysfiles shows:
fileidgroupidsizemaxsizegrowthstatusperfname
114872144-112820SomeDB
202792332684354561010486420SomeDB_log
The DB is in simple recovery mode. There are no open transactions (used dbcc opentran).
The server is running SQL Server 2014 and the DB is in compatibility mode SQL Server 2008 (100). It was upgraded to 2014 a month or two ago.
I have tried to re-size the log to 100mb, but any way I have tried (none gave errors), the log file remains the same size. I have tied to shrink the log file (through the UI and via DBCC commands) without success; no errors, but also no change in file size.
I have checked Log Reuse Waits, just in case, and as expected it showed “NOTHING” (select log_reuse_wait_desc, name from sys.databases)
I tried running a checkpoint, but that did not allow any resize or shrink to work.
I have tied creating large transactions to move the used point in the log file, in case this was the issue. I did this by creating tables that I drop after large inserts. While it shows me that the log space % used increased, the log file still does not allow the space to be reduced.
The following is what I was using for the transactions to get the log used.
BEGIN TRAN
select a.* into testtable from sysobjects a, sysobjects b, sysobjects c
ROLLBACK TRAN
Each insert creates 93,576,664 rows.
Running dbcc SQLPerf(logspace) :
DBLogSize(MB)LogSpaceUsed(%)Status
SomeDB2181.534.204890
Running dbcc loginfo:
RecoveryUnitIdFileIdFileSizeStartOffsetFSeqNoStatusParityCreateLSN
0211437342728192602640
02114373427211437424645701280
Do I just need to continue running large transactions until the log space used gets high enough to get the “end point” in the log to really move? Is there an easier way to accomplish this (I have several DBs that have the almost identical problem), what I am using moves the Log Space Percent Used about a percent on each execution.
We have reports in SharePoint integrated mode which are really slow when compared to native mode. I have been asked to research and give info on what exactly causes the delays.
Any articles which give me information as to what happens when a report is run from SharePoint server and where does it log.
I am checking some ratio numbers for our system engineers, those are
Read/write ratio?
Random/sequential ratio?
Read/write block size?
For Read/write ratio, I am using below query,
SELECT
m.type_desc
, CEILING(sum(num_of_bytes_read*1.0) / (sum(num_of_bytes_read*1.0) + sum(num_of_bytes_written*1.0)) * 100) AS 'Read %'
, CAST((sum(v.size_on_disk_bytes) / 1024.0 / 1024 / 1024) AS MONEY) AS 'FileSizeGB'
[Code] ....
Random/sequential ratio, I googled but cannot find a similar query to get the result?
I am writing a performance baseline test.
The first test writes 5000000 rows in one table. I realise this is not representative OLTP behaviour, but it worked me to start interpreting performance counters and to test several setups to be discussed with our server, storage and network administrators. This way we have been able to compare the results of different hard disks, Lun vs vmdk, 1GB vs 10GB network, AMD vs Intel, etc. This way I can also compare several SQL setups (recovery model, max memory config, ...)
The screenshot shows the results of 2 runs on the same server : Win2012R2, SQL2014, 16GB RAM.
In test 1 min/max server memory was set to 9215MB/10751MB
In test 2 min/max server memory was set to 13311MB/14847MB
The script assures the number of bytes inserted in the nvarchar columns is always the same.
This explains why the number of pages and the number of MB in the table are the same at the end of the 2 tests (column 5 and 6)
Since ca 13GB has to be written, the results of test 1 show the lead time is increasing once more than 10GB has been inserted (column 8 and 9) In addition you can see at that moment
- buffer cache hit ratio is decreasing
- page life expectance becomes "terrible"
- free list stall/sec increases
- lazy writes/sec increases
- readlatency increases (write latency does not)
In test 2 (id 3 in column 1 in the screenshot) those counters are not really influenced (since the 5000000 rows can all be stored in memory).
Now what I do not understand is :
Why the number of pages read (instance level) as well as the number of bytes read and the number of reads (databaselevel) is increasing extremely during run 1.
I expected to see serious impact on write behavior, since SQL server is forced to start flushing dirty pages once memory is filled. Well actually you can see here the number of writes (not the the number of bytes written) starts to increase faster in test 1 after 4000000 rows, but there's no real impact on write latency.
Finally I want to notice
- I'm the only user on this machine
- the table has a clustered index on a identity column
- there are no foreign key constraints
- inserts are executed using a loop, not one big transaction
- to monitor progress and behaviour/impact, each 10.000 loops the counters are stored using dmv queries
So I wonder why SQL Server starts to execute so many reads in test 1.
I had an existing table with lots of indexes.
As a test (fro speed) - I added a non clustered column-store index.
When I run test queries it always ignores my new column-store index. Why?
Should I remove the old indexes, leaving just the column store?
we are about to start a logshipping with the secondary server in read only mode.
I just wanted to know the disadvantages of using a read only on the secondary server.
I mean whether the transactional logs will be restored periodically or it won't be restored till users are connected on the secondary server.
I am trying to write a sql script that will basically test logins for Windows NT Similar to when you bring up SQL Studio, and do run as windows NT I want to ensure my rights are not removed from SQL Servers and if so send my nice DBA an email.
How to do the connect as ? and check my permission is still set to access database with db_datawriter, db_datareader etc
We have a reporting database which is refreshed daily from prod backup and later creating new tables/views/indexes as part of the refresh job. Is there a better approach we can implement in sql 2012/2014 for this scenario since we are planning to migrate to sql2014.
View 5 Replies View Relatedhow to put sql server database in suspect mode intensely and get it out from suspect mode to normal mode.
i am using SQL server 2008 R2
hi all,
one of my sql database went in suspect mode can any one advice why it has happened is it the problem with sql server . This has happend for the second time in 4 months .
thanks and regards
jk
As I know the database should be restored from backup and logs reapplied. That’s the first and generally recommended approach any time a DB is suspect, whether it’s from log or data file corruption.But if I have no backups then we follow below steps ( after Googling I found these steps on most of the websites ):
1.EXEC sp_resetstatus ‘yourDBname’;
2.ALTER DATABASE yourDBname SET EMERGENCY
3.DBCC checkdb(’yourDBname’)
4.ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
5.DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
6.ALTER DATABASE yourDBname SET MULTI_USER
Now my question is,
1. If after executing DBCC CHECKDB at 3rd step, it shows inconsistency.Then I will execute SET SINGLE USER WITH ROLLBACK & if after executing DBCC CHECKDB at 3rd step, no errors found. Then what will be the steps?
2. When we execute DBCC CHECKDB (' DBNAME ', REPAIR_ALLOW_DATA_LOSS) how can we recover lost data during process.As far as I know some data will loss and if it will happen then business will get suffered.
3. Why run DBCC CHECKDB() WITH REPAIR_ALLOW_DATA_LOSS if there are no consistency errors in the database?
I have a database in suspect mode. There is no backup of this database.
I've followed the troubleshooting and it isn't working out.
There are 3 steps:
1. execute sp_resetstatus
2. use alter database to add data file or log file to the database
3. stop and restart SQL Server
4. free disk space and rerun recovery
I've tried to make it, but the step # 2 haven't worked.
Then I tried step 3, however the database is still in suspect mode.
What should I do?
Is there something I am missing?
Thanks,
Fbio
Hi
my database went into suspect mode. i tried to reset the status database by "sp_resetstatus abc". It is giving the message
"Prior to updating sysdatabases entry for database 'cr2db', mode = 0 and status = 260 (status suspect_bit = 256).
For row in sysdatabases for database 'cr2db', the status bit 256 was forced off and mode was forced to 0.
Warning: You must recover this database prior to access."
please let me if you have any solution to this problem.
thank you in advance
ricky
Hi,
Could anyone please tell me steps to recover the database from the suspect
mode. The backup I got is very old. Can it be recovered without the backup.
Thanks in advance
John Jayaseelan
Hi,
When i tried to restore database from the Network m/c ...upto 60% of the restoration process went well,But after that i got message saying that
" Communication link failure " then database went in to LOAD/SUSPECT mode,
This happend in Active/Active Cluster environment and at the same point of time Fail over occured and resources moved to other node.
i tried by running SP_RESETSTATUS stored procedure to change the status of the Database ,But no use...
Then i deleted the Database and recreated and restored.. because it was a test server...BUT if it happens in Production what to do?????.....
Is there anyway to over come such situations...
Thanks in advance
Mar R
Dear Experts,
I'm using sql server 2005, and now i'm trying to restore with one backup. then i'm getting error saying that MSDB database in suspect mode.
how to make the DB to the normal mode....
please help me in this regard
Vinod
Even you learn 1%, Learn it with 100% confidence.
Database is in suspect mode and I cant get it out. Customer does not have a backup.........
getting Errors starting
Analysis of database 'SBM01' (7) is 100% complete (approximately 0 more seconds)
ex_raise2: Exception raised, major=79, minor=87, severity=22, attempting to create symptom dump
Error: 7987, Severity: 22, State: 1
A possible database consistency problem has been detected on database 'SBM01'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database 'SBM01'.
ex_raise2: Exception raised, major=79, minor=87, severity=25, attempting to create symptom dump
Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x19b591e0, EC = 0x19b59510
*
* User initiated stack dump. This is not a server exception dump.
Error: 3314, Severity: 21, State: 3
Error while undoing logged operation in database 'SBM01'. Error at log record ID (222:9976:477)..
Error: 9004, Severity: 23, State: 2
An error occurred while processing the log for database 'SBM01'..
Error: 3414, Severity: 21, State: 1
Database 'SBM01' (database ID 7) could not recover. Contact Technical Support..
In the work I came up against a situation when the working database at the end of day went to the Suspect Mode.And the archive was in many hours before. To transfer it in normal mode, until isn't made repaired base it was impossible. DBCC checkdb also refused to run because the database is in this mode.
View 7 Replies View RelatedRajiv Parekh writes "Since last few weeks on Startup of System few Databases moves to SUSPECT MODE.
This probelm is not daily but atleast 0nce in week.The day is is not fixed. Have Unistalled SQL & again Reinstalled, but the problem continues.
Please inform my Why this problem & what is the Solution."
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql
View 4 Replies View RelatedI have two databases like each other that one is the backup of another. Each DB have 2 filegroups. I want to replace one filegroup from one db to another. How do I do this? Or how do I backup and then restore?
View 3 Replies View RelatedQuery to show logins that don't have any permissions within the SQL instance? I'm tasked with doing some cleanup and have found some cases where the database was deleted or moved to another server but the logins that used it were not deleted. I'd like to identify them to research.
For instance a query to show logins that have no permissions in any of the existing databases would be handy. I'm thinking it would be complicated by the need to loop through all of the existing databases and then outer join it to the list of instance level logins. Going to try to write something like that but was hoping that a script already exists.
I have multiple SQL 2008 severs with databases. Also, 1 mirroring server in place.
Since my database count is increasing can i have only 1 mirroring server. Is there any limit of db at mirroring server. I would have approx. 150 databases.
I want to Replace The Big Log database with A new one ( A database with same structure).But current DB has many connection .
This is my plan :
1- Create a new database with same structure.
2- Rename current database to olddb with this code :
USE master
GO
EXEC sp_dboption CurDataBase, 'Single User', True
EXEC sp_renamedb 'CurDataBase', 'OldDataBase'
GO
3- Rename Newdb to current DB.
USE master
GO
EXEC sp_renamedb 'NewDataBase', 'CurDataBase'
is it true ? and Tsql code is ok ? (dont forget many of connection to curdatabase (that Is a log db) and loss some seconds data is not problems)
I have a job under the SQL Server Agent, which is configured to send an email notification on failure.
The job is scheduled to run every 30 minutes.
Is it somehow possible to configure it so that it only sends one email in case of subsequent failures instead of "spamming" my inbox every half hour?
I tried to copy db from server to server by sa user ( sql login) but this error raised and the copy failed
Executed as user: NT ServiceSQLSERVERAGENT. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:55:24 AM Progress: 2015-05-11 09:55:24.45 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2015-05-11 09:56:31.87 Code: 0x00000000 Source: 10_32_0_201_10_32_0_202_Transfer Objects Task
An error occurred while transferring data. See the inner exception for details. StackTrace: at Microsoft.SqlServer. Management. Smo. Transfer. TransferData()The Execution method succeeded, but the
[code]....
number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 9:55:24 AM Finished: 9:56:32 AM Elapsed: 67.892 seconds. The package execution failed. The step failed.
Is there a better way to deal with the virtual log files?...I see several approaches in dealing/decreasing the virtual log files for a database..want to know what's the best n safest approach, from the masters here?
View 9 Replies View Relatedin my environment I am running the SQL Server agent job.i am getting below message.
create a master key in the database or open a master key in the session before performing this operation” error
I have an environment with MS-SQL Server 2014 and always-on availability group configured (on 2-nodes).
I'm writing a Powershell Script which removes the database from the availability group (on the primary server) and then SHOULD drop the database on the secondary Server.
That works most of the time, but not always...
When it fails I get the error message:
Cannot drop database "Customer_2" because it is currently in use.
When i check the secondary DB-Server (sp_who2) while the script is running, i see that there is a process for the DB "Customer_2" with Status="background", Command="DB STARTUP" and LastWaitType="REDO_THREAD_PENDING WORK".
As soon as the script fails, this process for "Customer_2" disapears.
This happens always only on the second database in the availability group.
Why is the process still there, even after I removed the database from the Availability Group on the primary node.
If I remove the database from the availability group manually, the "background" process on the secondary node for that database disappears..
[URL]