I am using Sql Server 2012 in always on configuration with multi subnet failover clustering. Size of data file has suddenly increased, i dropped all the unnecessary table from database three days back. day before yesterday i tried shrinking data file using DBCCÂ command but it is taking too much time. is there any other option for deallocating the space.
View 7 RepliesI'm trying to move a log file of a database that is part of an availability group. I have been following steps from the article: [URL]
At first this worked fine for me in a test environment. When I tried it in a production environment the database on the secondary went into "Recovery Pending" state and I can't get it out.
I checked to ensure that the dB is looking in the right place for the log file, and it is. It just doesn't seem to actually use the new file. If I start and stop SQL service, the dB comes back up and is fine.
Here are the steps I'm going through and what is happening at each step:
--------------------------------------
:Connect DEVSQL --This is currently PRIMARY
USE[master]
GO
ALTER AVAILABILITY GROUP [DP-AG-DEV] MODIFY REPLICA ON N'DEVSQL' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO))
[Code] ....
All is good so far. Both the Primary and the Secondard have had their logical files changed, which has not taken affect yet because there has been no failover.
--Make SQL10 the PRIMARY
:Connect SQL10
ALTER AVAILABILITY GROUP [DP-AG-DEV] FAILOVER;
GO
SQL10 is now the Primary for this AG. And, as expected, the database [AG-Test] is in "Recovery Pending" because it is now looking for the log file in the new location. I need to move the file to the new location.
:Connect DEVSQL
--Enable XP_CMDSHELL
sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'xp_cmdshell',1
[code].....
This is where the script is failing, returning the error:
Msg 1468, Level 16, State 5, Line 5
The operation cannot be performed on database "AG-Test" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.
I can not get the dB to recognize the log file at it's new location.
If I restart the SQL Service, it comes back fine, which seems to indicate to me that it is not a permission problem and confirms that the file is in the right place.
How do I force SQL to look for the log file again without restarting the service?
Hi again
Damn, people, how to shrink log file. I've tried several times with
DBCC SHRINKDATABASE ( database_name , TRUNCATEONLY)
and
DBCC SHRINKFILE (log_name, final_MB, TRUNCATEONLY)
and the log still is _untouched_
I have got big table, about 190 mln records (15 GB) and after simple update the log is very big but unnessesery.
Michal Pajkowski
What is the best way to Shrink a Data file and Log Files.
View 1 Replies View RelatedHello,
we are running on sql server 2005 and in some of our production databases the log files are twice as bigger than the data files. we are planning to reduce the log file size. I saw the option on the management studio to shrink the log file. I just have some questions on this.
as long as we are doing on the production server can we do that while the database is online or do we need to take the database offline? any help would be appreciated. Thank you!
.
Hello everybody.
I have SQL2000 sp3 standard
with 50 db's
All db set for full recovery
and autoshrink
Backup done with Tivoli
full backup once a week
log backup done every 12 hrs
Problem .. shrinking logs
every 20 min I run job
DBCC SHRINKFILE (My_db_logFile) for every db
70% of the time I am getting
message similar to
-------------
Cannot shrink log file 2 (Wholesale_Log) because all logical log files are in use.
--------------
1. I checked with sp_who2
The is no activity on db 'Wholesale_Log' or any other db returning "Cannot shrink..."
Why i getting "Cannot shrink ..." ?
even if job runs right after backup of the log files ,I still have messages.
Thank you
Alex
I have a database file LEAR_Index(yes, it hold index data) from a havehave recently removed a bunch of data.It is about 120 Gb, 100Gb of which is not used. I wan´t to shrink thefile to lean 30-40Gb.I´ve been trying this:dbcc shrinkfile('LEAR_Index',40000)But to no apparent avail; the file did not shrink.I´ve tried using enterprise manager for this but it consistenlycrashes when performing this operation.#Any thoughs, idear as to what i might be doing wrong?
View 6 Replies View RelatedHi,
I have some doubts about Shrinking databases or files.
while shrinking a file, I learned that we are alllowed to shrink more than the minimum size of the file, does not it bring damage to the data in that file ??
Hi,
We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.
thanks
I've been tasked with taking over the support for a client's SQL Serverdatabase. I'm not a DBA by profession, I'm a software developer whouses SQL Server as a database designer.The clients have reported that the server is running out of disk spaceand examination shows that the log files for several of the databasesare at 5Gb or more.After reading around the subject I suggested the following sequence ofoperations:-- Select the name of the database you want to shrinkUSE MyDB-- Dump unwanted transactionsdump tran MyDB with truncate_only-- Get the name of the logfileSELECT * from sysfiles-- Having examined the rows returned by this use the log file....-- Shrink the file to required size (in MB)DBCC SHRINKFILE('MyDB_log', 10)Is this a reasonable approach? Please bear in mind that I'm pretty newto this, and I have many other tasks to do besides manage the server.A previous DBA has set up good maintenance plans etc. so everything isbeing properly backed up (well, I think it is)If this IS a good approach, would it be reasonable to do this on, say,a monthly basis as a scheduled job? Obviously the stepSELECT * from sysfileswhich gives us the physical name of the log file would be removed andthe job would operate explicitly on each log file for each database inturn.Many thanks for reading.William Balmer.
View 2 Replies View RelatedI have dataware house database and it's size is 2 TB with simple recovery model.I want to reduce it's size because everyday before loading table gets truncate.Is it best practice to shrink the datafiles?database having 5 data files and one log file.what is the best way to reduce?
View 7 Replies View RelatedI have a no of databases in full recovery model whose files are many times their datafiles. It is because these databases were copied from the development servers and in the development servers they were not taking the transactional log backups although once in the production server it is ensured that a transactional log is taken once in a day atleast. I plan to shrink the logfiles using the dbcc commands. However I am afraid that it may lead to severe defragmentation and performance hits.
We are using Sql Server 2008R2 enterprise edition which is clustered.
In this context my questions are:-
1)What is the best course to do the shrinking of log with out defragmentation?
2)Can I do the shrinking when the database is in use or is online in production?
3)Will the shrinking of the logfile improve the performance in any manner like that of the i/o operations or paging?
4)Can I do the shrinking of the log files alone without the shrinking of the corresponding data files?
I have a database which has log file size 300 GB. As the drive is filling up i need to clear the space on the drive, for that i have to shrink the log file.Â
Unfortunately i dont have option to take backup of the database.And i am not able to shrink the file now. Is there any way to shrink the log file with out taking backup of it ?
If possible how data will be organized in MDF and NDF files. Because we have a MDF file and 11 ndf files. when we are loading the data into one table, it is using all the files in equal ratio. Is there any address reference of ndf files in MDF file?
View 5 Replies View RelatedI have a requirement to
a. Read data from Different CSV files.
b. insert and update data to Data base in multiple table using joins.
This execution runs for 1-2 hours.I can use C# with Ado.net, but only concern I see is if in between execution fails due to some connection or other error. All insert data has to be cleaned up again.I feel writing and Store procedure inside transaction, which will take path's for CSV file as input and insert data in database. using transaction we will have flexibility rollback to original state.
how to shrink log files in SQL 2014 alwaysOn ?
View 1 Replies View RelatedHi all,
does the shrinking of the database affect the data in the database, such as will it change the data, or deleting the data away?
Thanx
(SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.
What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.
Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.
(DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)
Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.
Thank you in advance for any input!
On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
View 1 Replies View RelatedHi there,
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Kind Regards
I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 4 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).
On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:
dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))$(ESCAPE_DQUOTE(INST))"
The error message is as follows:
Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2
Job Name collection_set_3_upload
Step Name collection_set_3_upload_upload
Duration 00:00:07
[Code] ....
I know I can prevent this error message by enabling readable secondaries, but we do not want this.
I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.
In addition, if I set the one failing AG group to readable secondary the job succeeds. So that means that 3/4 work fine, only this one is having an issue.
how to add the Secondary Data file to the Database that is a part of the Always on Availability on SQL Server 2012.
View 3 Replies View RelatedI proposed on a new server that we separate Data Files, Log Files, tempDB, Backups, etc. onto separate LUNS on a SAN with High Speed Solid State Drives.I was told that with the new technology with solid state SAN's that it would decrease performance and that it did not work the same way as it did when you had RAID 5's etc.I thought that if things were cared out correctly by a SAN Administrator they would know how to configure for optimal performance.
View 2 Replies View RelatedWe have several read-only nodes in our AlwaysOn cluster, which are set to use Synchronous-commit mode, which ensures that the logs are updated on the read-only nodes before any update statements complete. Even with this option, if we query a read-only node before the logs have been processed, we can read old data. I would like to know a strategy to ensure that a read-only query will definitely return up to date information. I had an idea that if I just used a different transaction type, like Serializable, that it might block the read-only query from actually getting the data until after the log file was processed, but I have not tried it, yet.
I would like to move more queries to the read-only nodes, in an effort to offload CPU utilization from the primary node.
Need to confirm if we can add space(increase data file size) for the database which is configured for always on similar to that of mirroring or we need to follow any different procedure.
I have a requirement wherein the datafiles on both the primary and secondary replica got full, if i add space to the primary database will it automatically get added to the secondary replica or not?
Background information: SQL 2014 Ent. highly volatile OLTP environment. We generate 10 - 12 GB compressed transaction log backup files every 15 minutes.
Currently - we have two-node A/P cluster residing on flash array. Need to leverage AlwaysOn to offload processing. Replica server with have Flash storage. Replica node has same CPU and memory footprint. 10GB connection between nodes. Anyone generating such large transaction log for 15/30 minute time period?
Hi,I have my create statments for tables, procedures, views, etc inindividual Transact-SQL script files (.sql).I wnat to write another script file that executes these scripts in thecorrect order to create the database.What is the syntax for executing script files from Transact-SQL?Thanks, Phil
View 1 Replies View RelatedI'm wondering if its possible to return the number of files in a folder suing something like DIR command.I'm wanting to do something like, If count(DIR) >0 then do something
else End
exec master.dbo.xp_cmdshell
'dir C:Test'
I am on SQL Server 2008 R2. I have a table that contains a field called [Location]. In that field is a UNC path to the physical file on the repository. Is there a way in SQL Server that I can say give me the select sum([Location] UNC file) where criteria? I saw some posts about xp_filesize or xp_GetFileDetails, but I do not see them in master. I am unable to add anything and wondering if there is any native functionality that would allow me to accomplish this!?
View 4 Replies View RelatedI am using SQL 2012 SE. I am trying to move .mdf and .ndf files after a database is detached. Here is my code that is just to copy the mdf file. I am testing it against this file now and if it worked then I would do the move ldf file the same way.
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH OVERRIDE
DECLARE @cmd nvarchar(4000)
[Code] ...
The only message is I see while the query is executing is :Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
If I manually copy over the file it takes 30 seconds since the file is only 2GB and the script takes 45 minutes and still executing.
I have a job which copies .txt files 24 hours 7 days a week to c:TempSource
What I am planning to do is copy the files from one location to another say c:TempTarget
So I have written the Powell shell script and when i put that in the sql agent job i get below error ;
A job step received an error at line 5 in a PowerShell script. The corresponding line is '$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'A parameter cannot be found that matches parameter name 'Property'. '. Process Exit Code -1. The step failed.
$sourceFiles = "c:TempSource*.txt"
$targetFolder = "c:TempTarget"
$files = Get-ChildItem $sourceFiles
$newestFile = ($files | sort LastWriteTime -Descending)[0]
$filesToMove = $files | Where -Property "Name" -NotLike -Value $newestFile.Name
$filesToMove | ForEach { Move-Item $_ $targetFolder }Â
I have more than 500 CSV files with a similar structure [Same column name and same data format]. I would like to load these files in a database table on the SQL Server 2014 database.
View 21 Replies View RelatedI have around 100 XL Files in a folder ,i want to import all the files dynamically and load all the data in a single table in sql server 2008. Without using SSIS i want to query using openrowset.
View 11 Replies View Related