begin transaction
begin try
insert into [EthnicRace] select * from [OEPRD].[CIS_Source_Data].[dbo].[EthnicRace];
insert into [MilestoneOwner] select * from [OEPRD].[CIS_Source_Data].[dbo].[MilestoneOwner];
insert into [PS_ACAD_CALTRM_TBL] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_CALTRM_TBL];
insert into [PS_ACAD_DEGR] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_DEGR];
end try
A job on our production server goes out to Oracle and gets raw data from our student system once per night. A job on our development server then uses the above coding to copy that raw data from production to the tables in our development system. My question is, will the log continue to grow until the "end try" is encountered or is it cleared at the end of each INSERT statement?
What is the recommended size and file growth for a database and log file? We will be storing approx 10000 records a day.Currently we have the following:
CREATE DATABASE Dummy ON PRIMARY ( NAME = Dummy_data, FILENAME = 'D:....DATADummy.mdf', SIZE = 250MB, FILEGROWTH = 25MB ) LOG ON ( NAME = Dummy_log, FILENAME = 'D:....DATADummy_log.ldf', SIZE = 50MB, FILEGROWTH = 5MB ) ; GO
My customer got a total hard drive failure.After sending it to drive recovery specialist we were able to recover the LDF log file (MyDB_0.LDF).But the MDF file was completely destroyed (MyDB.MDF).They have a good full backup from a month ago.
1) Installed SQL Server 2012 on a new PC 2) Created a new database of same name (MyDB) - with same MDF and LDF file names as original 3) Took the new database offline 4) deleted the MDF and LDF files of the new database 5) put "MyDB_0.LDF" in the place of the LDF file I just deleted 6) put the database back on-line 7) after hitting F5 to refresh databases - it shows "MyDB (Recovery Pending)" 8) tried to do Tail Log Backup with this command BACKUP LOG [MyDB] TO DISK = N'C:BACKUPMyDB_TailLog.bak' WITH NO_TRUNCATE
And I get this error...
Msg 3447, Level 16, State 1, Line 3 Could not activate or scan all of the log files for database 'MyDB'.
The sad thing is I know we can get this data back using ApexSQL-Log. I can see all the transactions since the last full backup in this program - so the log file is not damaged. But my client doesn't want to pay the $2000 fee for this software.There has to be a way to restore this data, without having to purchase a third party tool.
I checked quickly one network and I ran into the folders; I found out the folder MSSQL backup, nothing strange so far. Within that folder, anyway, I found out several backup and one very huge file (datawarehouse) with extension FILE. I am wondering what can be? I got datawarehouse mdf of course and datawarehouse log but what is that huge file (1 TB)?
Using SQL Server 2000. When does SQL flush or clear the procedurecache? I am dynamically creating and dropping stored procedures (SP).Does SQL clear the cache for the SP that has been dropped? If not,when the SP is recreated (with the same name), does SQL use theexecution plan from cache?Thank you in advance.Jack
I have a report created in Reporting Services that contains 5 parameters. Two are text [date] fields, and three are drop-down multi-value parameters. One is driven from the result of another, and both of those work fine. My issue is with the final multi-value parameter, which is simply a list of customers. It gets populated from the data set without a problem, and I can select from 1 to all of the options. However, on submitting the report. This one parameter gets cleared out, the report does not run, but instead waits for me to select the customers (again) and resubmit.
Occasionally, I can select a few customers and it will, in fact, run the report. However, 90% of the time it just clears the field and waits.
I can trace the database, and everything looks normal - on submit nothing is sent to the database, so it's not like there's a problem with the query. I've verified the customer table and all records look similar - I don't see any obvious data issues.
maybe it's a silly question... I've created a new report by the wizard (including a data query). In the dataset frame my dataset contains the queried items. But when I change to data view and back to layout view in the designer frame, my dataset is cleared. Is this a bug or normal behaviour?
I have installed sql server 2005 in cluster. after all the process is finished the memory usage is showing 2 gb where as in the activity monitor there is no process running. what should i do to clear the memory automatically.
If i stop and start the serverice it is cleared, nor do i want to run any command to clear the memory.
I have a very odd problem. I have a package which uses some custom tasks that were written in C#. When the package is deployed to our production server, *some* of the property values for *some* of the tasks are cleared. For example, I have these five tasks:
All of them inherit (of course) from Microsoft.SqlServer.Dts.Runtime.Task. All of them have custom members (some similar, some different), and of course, different implementation (though they are mostly the same). This test package has one instance of each of the different tasks.
As I said above, when we deploy to our production server, *some* of the property values for *some* of the tasks are cleared -- but when deployed to our dev server, everything remains intact.
Here is what is cleared:
- On 4 of the 5 tasks, the Description property (inherited by Task) is cleared, but the other one remained intact - On 3 of the 5 tasks, the Connection property (custom property in all tasks) is cleared, but the other two remained intact - 3 of the tasks have other string properties that were set, and all of these were cleared
We can reproduce this on two different production servers, and these two servers have some different configurations, suggesting these would not be the culprit:
- They have different service packs (one is build 2047, the other build 3042) - One has the custom SSIS components installed (in the GAC), the other one does not
Our development server, where the package is deployed as expected, has build 2047 w/ the components installed.
Here are the packages, where you can compare and see the differences (using a text comparison tool):
Dev-GOOD.xml Prod-BAD.dtsx
These were created after being deployed by importing within a Visual Studio SSIS project from the server.
Any suggestions would be *greatly* appreciated, as we are totally stumped as to why this is happening.
EDIT: Additional clues, this package is deployed to the MSDB. If it's deployed to the File System, it remains unmodified.
I have a report that includes two multi-valued parameters. In the Default Values section, I choose 'from query' and select dataset and value field. In the Available Values section, I choose 'from query' select the same dataset and value field, and in the label field I select the relevant label field. When I run the report my multi-valued parameters look like I selected the option 'select all' (all options are selected). How can I keep the multi-valued parameters cleared from selections until the user select his choice? Thanks in advance.
I am trying to shrink the log file as it is eating my disk space. increased to 100GB..message after running the query"Cannot shrink log file 2 (mydatabase_log) because the logical log file located at the end of the file is in use.
(1 row(s) affected) DBCC execution completed. If DBCC printed error messages, contact your system administrator. " (1)use mydatabase alter database mydatabase set recovery simple with no_wait dbcc shrinkfile(mydatabase_log,1) database [mydatabase] set recovery full with no_wait
(2)USE mydatabase; GO Truncate the log by changing the database recovery model to SIMPLE. ALTER DATABASE mydatabase SET RECOVERY SIMPLE; GO Shrink the truncated log file to 1 MB. DBCC SHRINKFILE (mydatabase_log, 1); GO -- Reset the database recovery model. ALTER DATABASE mydatabase SET RECOVERY FULL; GO
In SQL 2012 I'm trying to delete a log file (both physically and logically). There are two log files and one of them is unnecessary. When I click 'remove' in SSMS it will delete the log file, but then when I go back under database properties it's still showing up even though the file has been physically removed from the OS. I'm wondering what steps I can take to get rid of the file permanently?
If we want to change something about a custom server side trace in SQL 2012, do we wipe the current trace with sp_trace_setstatus <trace_id>, 2 and then re-create it with the new definition? It looks like there's no way to modify it in place.
One of our database came to Restoring mode. I suddenly stop my SQL service and Copied only MDF files again Started SQL service ,unexpectedly i dropped the Database. Now i cant able to attach the database only with my MDF file.
I tried below Scripts. All scripts shows same error.
EXEC sp_attach_single_file_db @dbname='PhoenixPolice', @physname=N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf' GO CREATE DATABASE PhoenixPolice ON (NAME = N'PhoenixPolice', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH_REBUILD_LOG GO CREATE DATABASE phoenixPolice ON (FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLPhoenixPolice.mdf') FOR ATTACH
All scripts shows below error
File activation failure. The physical file name "F:Microsoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATAPhoenixPolice_1.ldf" may be incorrect.
The log cannot be rebuilt because there were open transactions/users when the database was shutdown, no checkpoint occurred to the database, or the database was read-only. This error could occur if the transaction log file was manually deleted or lost due to a hardware or environment failure.
Msg 1813, Level 16, State 2, Line 1
Could not open new database 'PhoenixPolice'. CREATE DATABASE is aborted.
i have .bak file downloaded from internet , and i also have istalled sql server 2012.my problem that i can not restore this .bak file and get this error massage :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended) - .bak file version = 661 10 50 1600 = sql server 2008R - my sql version = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
On one of our SQL Server 2008R2 instances the MSDB log file (MSDBLog.ldf) has grown to 300GB+. The data (.mdf) file is only 3GB.
Neither DBCC Shrinkfile(MSDBLog) nor an interactive "Tasks / Shrink / ..."
I've already deleted much of the historyThe MSDB database is in "Simple" recovery modeI've done a full backup (in case the log was "waiting" for a backup)
I hesitate to do things (such as as Detach / Attach) with MSDB that I might do with a user database.
I have to perform disk maintenance on current drive - Drive 'D' where it has sql data (mdf file) and I have added new drive - Drive 'E' By the way Drive 'C' have the program files for SQL Server 2008 R2 What is the correct process to transfer sql data (mdf file) from Drive 'D' to Drive 'E' and later remove Drive 'D' from the server.
In tweaking performance of tempdb by adding add'l data files I want to reset back to defaults and remove all add'l files I've created. I was not able to do it for most as they were in use, but by starting the server in single-user mode with all other sql services shut off, and using sqlcmd I was able to use the ALTER DATABASE tempdb REMOVE FILE <tempdev#> to remove the files... except for one.
Restarted SQL server, and tried the ALTER DATABASE ... REMOVE FILE again but am always denied with the message that the file can't be removed b/c it's still in use.
I also tried to shrink it with EMPTYFILE but that also fails with the message that a page is a work table page and can't be removed.
I really need to get tempdb back to just one data file but am unable to find a way to remove this last data file.
For one of our database we have an issue where its log file got increased rapidly last week on Fri and Sat. The database is on SQL server 2008 R2 with compatibility level at 80. Please see below log grow events :
First, we thought Index maintenance like Re-index and update stats could have been the reason, but when check the schedule that job ran on 16th using below code:
USE ABC GO EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)" GOEXEC sp_updatestatsGO
I know above is OLD fashioned, but we believe that should not be the major cause here? How can i determine what happened on 14th and 15th which cause the event to trigger and log file bumps to 80 and 70 GB both days.
I have a query to pass .csv file data into sql table using bulkinsert . I used :
CREATE TABLE #CSVTest (CouponCode VARCHAR(200)) /* Insertion From csv Source To Table */ BULK INSERT #CSVTest FROM 'C:Apple.csv'
[Code] ....
This worked very fine to me. Now, the thing is i should pass the filename as parameter and so that what ever file i specified in execute command , query should take that file and push that file data from .csv file to sql table.
I have a file in Fire bird Database (30 GB with .ydb extension). which needs to be restored to SQL Server. I Have created a linked server and done it but it is taking very long time to update the records.
We try to run a stored procedure in management studio - and we see the following error - " Executed as user: "Some User". Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[SQLSTATE HY000] (Error 0) The log for database 'Some-database' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2343114:16096:197).
Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314) During undoing of a logged operation in database 'Some-Database', an error occurred at log record ID (2342777:81708:1).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database. [SQLSTATE HY000] (Error 3314). The step failed.
1.) Taking a database out of an availability group, 2.) Setting the recovery to simple, 3.) Shrinking the log file, 4.) Setting the recovery mode back to full, 5.) Then backing it up.
I need to restore the file to my secondary server with replace and non recovery mode. I am having trouble performing that call? I have the code to reestablish the database to the availability group if I can get the restore feature working.
I added a secondary data file to TEMPdb yesterday and gave it a wrong location by mistake. If I try to change the location, then I am getting an error now. I think that is because TEMPdb is in use and that is why I cant change it's secondary file's location. Do I need to take TempDB offline and then change the secondary file's location??
We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.
Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?
SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) - Enterprise Edition (64-bit)
My understanding is that the log file is not supposed to grow if the database is under simple recovery mode.I am in a situation where the log grows if do any inserts that involve millions of rows.How do i make sure that it does not grow?
I 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?