I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
I wrote a custom procedure to shrink the db log files alone. It works well always but yesterday in one of the server the job execution was failed.
Error msg:
: The log for database 'ReportServer$DB' failed to grow while shrink in progress. Please retry. [SQLSTATE 42000] (Error 50000) Error 9020, Severity 16, State 2,
The code is v simple. It just shrinks the db log files of all user dbs.
I also checked and confirm you that against all dbs the auto-shrink feature is disabled.
I am trying to reorganise the log files on a server, (long story short they are fragmented so I want to shrink and reset the initial size and growth) and I am unable to shrink them. When I run the following:
use test
DBCC SHRINKFILE(test_log, TRUNCATEONLY)
--or
use
DBCC SHRINKFILE(test_log,2, TRUNCATEONLY)
I get the following message:
Msg 8985, Level 16, State 1, Line 1
Could not locate file 'test_log' for database 'test' in sys.database_files. The file either does not exist, or was dropped.
I get this message for every database on the server. I got the logical name of the file using sp_helpfile and have checked it against sys.masterfiles, sys.database_files and sys.sysaltfiles, all match up and confirm the name 'test_log'.
I rebooted the server last night and was able to shrink the first couple of .ldf's I tried so I presumed it was fixed. This morning when I try again i get the sanme error, I don't see anything in the SQL server or system logs that indicates a change.
I am able to add new log files and remove log files, however if I add a new log file (test_log2) and then try and truncate that file I get the same error.
Hi,
I was looking for system databases diagram(poster). If any one has this could you please point me to the URL where i can find it!!
Thanks in advance.
Is it possible to manually force/call/start the system AUTOSHRINK process? I have an issue that appears only when the engine shrinking process is running and I need this to reproduce my bug.
I know how to start a "regular" database shrink process with:DBCC SHRINKDATABASE(xxxx);, but this is not the same as one started from the database engine.
Queries against DMVs like sys.dm_XXX causing blocking in user databases? I have never read that querying DMVs has caused blocking in user databases but that question recently came up in my environment.
View 5 Replies View RelatedFollowed this article to move my model and msdb databes but I think I messed up. http://msdn.microsoft.com/en-us/library/ms345408.aspx
The instance will not start because it says one of the files does not match it's primary file (not sure if it's model or msdb)
I can bring the instance up in single user mode (NET START mssqlserver /f /t3608) but when I try to do a query to re-do my alter statements, it says i can't attach because only one seesion is allowed. in 2000 i used to be able to start query analyzer without starting enterprise manager..
When i try and start with a minimal config (sqlservr -c -m) it won't start. I get an error saying that one of my files does not match it's primary file (either model or msbd don't know which) any ideas????????
Hi,
Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.
Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.
However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon.
The temporary solution is to turn the index rebuilds off.
I have been working on a script that will:
Cycle through each database and within that database:
Go through each table
Run a DBCC DBREINDEX on the table
Move on to the next table
Once the reindexing of one database is complete
IF the database is not in simple mode
Backup the transaction log
Run a DBCC SHRINKDATABASE with the required amount of free space
Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits.
Now the script works fine excluding the shrinkdatabase, I always get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000]
Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000]
Where I'm indexing the INF_Prod database.
A DBCC LOGINFO shows something along the lines of:
Code Snippet
BACKUP LOG successfully processed 45162 pages in 152.607 seconds (2.424 MB/sec). [SQLSTATE 01000]
FileId FileSize StartOffset FSeqNo Status Parity CreateLSN
----------- ---------------------- ---------------------- ----------- ----------- ------ ---------------------------
2 253952 8192 29618 0 128 0
2 253952 262144 29621 0 64 0
2 253952 516096 29620 0 64 0
2 278528 770048 29619 0 64 0
<cut cut>
2 9043968 370737152 0 0 0 29800000001528500007
2 9043968 379781120 29802 2 64 29800000001528500007
2 9043968 388825088 29801 0 64 29800000001528500007
Clearly there is something in the log file towards the end.
However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on.
I've tried doing Full backup + Transaction log + Shrink, it fails.
I've tried waiting 10minutes in the script + shrink, it also fails.
However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time.
However in the script it always fails no matter what I do.
Where am I going wrong here?
Regards,
Gareth
I have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create that user and give datareader role to him. or is thr any shorter way to do so????
View 8 Replies View RelatedScript to find a value in all databases ? for example I want to find a value contain word “ SQL” in all databases so I will get the information in which database and which table it exists.
My script is only for finding a value in one database.
I have log shipping enabled on databases(primary and secondary) and works fine. I need to implement TDE on the database. I have experience on implementing TDE on databases which are not used for log-shipping.
What are the steps needed to setup TDE which are involved with log-shipping.
I would like to migrate around 15 databases in production server to the new production server. The biggest database is 80 GB .
Wondering is there any fastest way to do that with very low downtime ?
What I can think about is shrink databases files and perform attach – detach .
What are the potential risks of keeping data , log , tempdb backups etc in the same drive although in different folders ?
View 4 Replies View RelatedI have a sharepoint content database in sql 2008 R2 (WSS_Content) that is at 230Gb size, but has 40% of it is empty space. This is because we have removed a large amount of old content from sharepoint. The log file is fine. I have 60GB left in my drive that host the database files. I would like to shrink the datafile to get disk space back. I found that under the files property, the WSS_Content data file's initial size is 228702 MB (220 Gb or so).
When i try to do a shrink file (data file) from management studio, i see the 60 GB of drive space keep dropping. So i have to kill the process. what i should do to reduce this data file.
why it keep using up all the free space in the drive when i try to shrink the data file?
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies View RelatedHow can you install the System Databases to a drive other than the default?
I want the Data Files to be installed on D:MSSQLData and the log files to be stored on D:MSSQLLog.
For better, quicker answers on T-SQL questions, click on the following...
[URL]
For better answers on performance questions, click on the following...
[URL]
Find unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
I have a new problem with doing a restore of a number of databases using powershell. The script I'm using is based mainly on this one (Part 2 in particular): [URL] .....
The problem I'm having is around the RedgateGetDatabaseName function. My hunch is that its down to the different version of red gate and how sqlbackup works. Basically when the call is made to the function it is returning both the Database Name and the number of row's that the SQL command in the function has ran. I've tried to include a SET NOCOUNT ON at the start of the SQL command in the function but its still returning the now count.
Currently our database size is around 350G. It will grow up to 1.5 TB
We have the
Auto create statistics option :True,
auto update statistics option :True,
auto update statistics asynchronously option : False
at database level
we have a weekly job, update statistics running very long time. It is created through maintenance plan using the option full scan.
Previously they tested with sampling but instead of full scan running with the sampling effected the queries.
Is there option to avoid the long time job duration.
If we didn't run the statistics manually what will happen? How do you maintain statistics with large databases
I need to generate data models of all databases for an instance. How can I accomplish this?
View 3 Replies View RelatedI have 2 databases: one for our intranet & another for our internet website.In the intranet database, we have a table called "Clients". A new client can be added in one of 2 ways:
1) One of our employees manually adds the client via the intranet
2) A new customer subscribes to our services via our website, thus becoming a new "client" (when subscribing online, we also add a record to an "Accounts" table located in our internet website database, but we'd also like to add a record to our intranet's Clients table as well).
In the client table is a field called "CreatedBy" which expects the UserId of whoever created the account. Again, this UserId can belong to either an employee via the intranet or a new customer via our internet website. how do I distinguish where the UserId in the dbo.Clients.CreatedBy field is coming from?
My 1st thought was to append a negative to the CreatedBy UserId# if it came from a customer via the website, but that just seemed too quirky (i.e. if an employee added a client and his UserId was '10', the Clients.CreatedBy would be '10'. If a customer subscribed via the website, the Clients.CreatedBy would be '-10'. Where customers can once again sign up online. So, I need a way to keep these databases separate but keep track of which db a user's coming from when we create a new Client record.
I am working on a task. Currently we are taking a database backup and keeping that backups in a folder. The backups doesn't have time stamp on it. My task is need to get the latest backup and copy that backups into some other server and then restore the database from there.I am planning to create SSIS package.Do we need script task for this task.How to get the .bak with latest create or moidified date. For now we doesn't have timestamp so need to go based on modified date?
View 9 Replies View RelatedWe have two databases with same schema and tables (same table names, basically main DB and a copy of the main DB). following is example of table names from 2 DBs.
CREATE TABLE #SourceDatabase (SourceColumn1 VARCHAR(50))
INSERT INTO #SourceDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #SourceDatabase
DROP TABLE #SourceDatabase
CREATE TABLE #ArchiveDatabase (SourceColumn2 VARCHAR(50))
INSERT INTO #ArchiveDatabase VALUES('TABLE1') , ('TABLE2'),('TABLE3') , ('TABLE4'),('TABLE5') , ('TABLE6')
SELECT * FROM #ArchiveDatabase
DROP TABLE #ArchiveDatabase
We need a T_SQL statement that can create one view for each table from both the databases(assuming both databases have same number of tables and same table names). so that we can run the T_SQL on a thrid database and the third DB has all the views (one view for each table from the 2 DBs). and the name of the view should be same as the tables name. and all 3 DBs are on the same server.
the 2 temp tables are just examples, DBs have around 1700 tables each. so we ned something like following for each table.
CREATE VIEW DBO.TABLE1 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE1] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE1]
CREATE VIEW DBO.TABLE2 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE2] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE2]
CREATE VIEW DBO.TABLE3 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE3] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE3]
CREATE VIEW DBO.TABLE4 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE4] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE4]
CREATE VIEW DBO.TABLE5 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE5] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE5]
CREATE VIEW DBO.TABLE6 AS SELECT * FROM [SourceDatabase].[dbo].[TABLE6] UNION ALL SELECT * FROM [ArchiveDatabase].[dbo].[TABLE6]
im trying to measure the amount of CPU used in a backup and restore, using the SQL Server Databases: Backup Restore counter in Perfmon.
I can select The counter, but it does not let me select an instance database to monitor.
see image:
I have 3 instances on my machine, 2 sql 2005 and 1 sql 2008. why do these instances not appear? i dont know how to set up this monitoring.
if i type LOCALHOST or 127.0.0.1 in the "Select counters from computer" text box i can see all databases under my sql 2005 instances, but not the ones under the 2008 instances. I have disabled all sql server 2005 services and can confirm none of the sql 2008 instance databases are showing up.
this is a huge problem, especially for when i start stress testing sql server 2008 in preperation for upgrading.
anyone any ideas on why i cannot see sql server 2008 instances in perfmon? I have tried from a remote pc also with the same results.
In the notification tab of job properties, I can see three options in the drop down list at the right hand side.
When the job succeeds
When the job fails
When the job completes
In which system table these options are saved ? Is it possible to add a custom option in that dropdown list.
Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedI was contacted by the SAN team to test backup/restore of larger databases using a split-mirror backup (BCV) or clone that is taken from production db server and copied to another sql box. They want to use this process once a week. I see the mounted drives with the data/log files. All looks good. Initially I attempted to attach the databases and received (Unable to open the physical file db.mdf Operating System Error 5 Access is denied). I manually granting SQLServerMSSQLUser$<computer_name>$<instance_name> on all of the physical files 20 total. That worked.
Since this will be weekly, the SAN team performed the copy again and now none of the databases can communicate with the newly copied files. NTFS permissions need to be set again. I'm getting (Operating System error 21: the device is not ready). Is there something that I'm missing in this process how the vendor BCV clones the data and SQL communicates with the copied files as I was thinking it would be more automated process?
Is there an easy way to compare the contents of objects between 2 different databases? For example, say I had 2 databases, My_DB_1 and My_DB_2, each with a SEC_User table. Say I wanted to do an object-to-object comparison between databases to see if there were any differences. Here's some sample SQL:
use My_DB_1
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
use My_DB_2
select * from sys.dm_sql_referencing_entities('dbo.sec_user', 'object')
Say that the result sets above both returned a SEC_GetUser sproc object ref. Is there a way to write SQL that will compare the SEC_GetUser sprocs (and other objects in the above rowsets) from both databases? For example, if My_DB_1.SEC_GetUser returns an extra column in the result set than My_DB_2.SEC_GetUser then I'd like my comparison SQL to return a single column "IsDifferent" with SEC_GetUser as a row....
I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
I have system database and user database file are present in G,H and W drive.The process is going to be - copy data from G to S, H to T, W to U. Rename G to X, H to Y and W to Z. Rename S to G, T to H and U to W. Reboot the servers. The original G, H and W will then be X, Y and Z. The old S will be the new G, old T will be H and old U will be W. My question is that after doing this whether my SQL server will start or not
View 8 Replies View RelatedI am working towards automating the process of testing our backups. For the meantime, I do it all manually - I copy the backup files (full + transaction logs) to our test server and then run the restore script. Once database restored I run the DBCC CheckDB. The results of checkdb I manually upload to our Sharepoint portal as proof that the backup file is intact with no errors.
here are some ideas I have but have not yet tested:
Create a maintenance plan with each 3 jobs:
--> Powershell script to copy the files from Prod server to Test server - add this scrip to Job1
--> Powershell script to restore databases files - add this script to Job2
--> Run the DBCC in powershell (yet to find if possible in PS) - add this script to Job3
I would like to use seperate jobs as to get a report on the duration and status of each job
Would also like to get the results of the DBCC Checkdb as proof that no errors were found for upload to our Sharepoint portal. Dont know if possible via the job.
How to restrict resources usage based on individual Databases in resource governor?
We have many databases in one instance; I would like to restrict resource usage to each database respectively.
I created 2 pools as pool_login, pool_DBNAME, and 2 workload groups as GroupLogin,GroupDBNAME, and also the classifier function.After setup above, I use following statement to check what sessions are in each group .
Even if there are spids which are accessing database DBNAME, I can’t see that they fall into the group GroupDBNAME and pool pool_DBNAME.
SELECT s.group_id, CAST(g.name as nvarchar(20)), s.session_id, s.login_time, CAST(s.host_name as nvarchar(20)), CAST(s.program_name AS nvarchar(20))
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_resource_governor_workload_groups g
ON g.group_id = s.group_id
ORDER BY g.name
GO
Following is the code to create pool, group,classifier function:
USE master
GO;
-- Create a resource pool pool_login.
CREATE RESOURCE POOL pool_login
WITH
[Code] ....
-- Create a workload group to use this pool.
CREATE WORKLOAD GROUP GroupLogin
USING pool_login;
GO
CREATE WORKLOAD GROUP GroupDBNAME
USING pool_DBNAME;
[code]....
-- Register the classifier function with Resource Governor.
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1);
GO