DB Engine :: When Is Log File Cleared

Jun 9, 2015

Recovery model: Simple with Full backups

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?

View 5 Replies


ADVERTISEMENT

DB Engine :: Memory Table Not Cleared If Created Inside A While Loop

Oct 29, 2015

If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.

But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.

This can be reproduces with the following code:

DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)

[Code] ....

/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2

Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).

View 2 Replies View Related

DB Engine :: Recommended Size And File Growth For A Database And Log File?

Sep 22, 2015

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

View 3 Replies View Related

DB Engine :: How To Restore From Current LDF Log File And Old BAK Backup File

Jun 11, 2015

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.

View 13 Replies View Related

DB Engine :: Extension FILE Among Backup File

May 8, 2015

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)?

View 6 Replies View Related

DB Engine :: Cannot Create A File When That File Already Exists

Aug 18, 2014

Executed as user: S233683-AD01S233683NJ3SQL05. ...at file already exists.' [SQLSTATE 42000] (Error 22048)  ERROR -- Could not backup database: master - BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 50000)  xp_create_subdir() returned error 183, 'Cannot create a file when that file already exists.'

View 2 Replies View Related

When Is Procedure Cache Flushed Or Cleared?

Jul 23, 2005

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

View 2 Replies View Related

Multi Value Parameter Cleared On Submit

Aug 17, 2007

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.

Suggestions?

View 4 Replies View Related

Dataset Cleared After Changing View?

Feb 12, 2008

Hello,

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?

Regards
Rayko

View 2 Replies View Related

SQL Server Memory Not Cleared Automatically

Mar 3, 2008



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.

View 1 Replies View Related

Property Values Cleared After Package Deployment

May 14, 2008

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:

FP Export File Logger
FP Import File Logger
FP Import Table Logger
FP Instance Logger
FP Job Reader

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.

Thanks in advance.
Jerad

View 2 Replies View Related

SQL Server Admin 2014 :: Procedure Plan Cache Is Cleared Automatically

Sep 17, 2015

The plan cache is cleared automatically (every 30-60 seconds) on our high load OLPT server.

Can't find a reason for it. I have been logging procedure cache thru dm_exec_cached_plans, every second for a couple of minutes.

It shows that it doesn't grow beyond 200Mb. SQLserver has 64Gb available.

DBCC FREEPROCCACHE is not executed.
dm_os_memory_clerks shows the same 200Mb max (approximately).

I disabled the SQL Agent, still happens, so it's not a job causing the problem.

I disabled the Resource Governor, still the same problem.

View 9 Replies View Related

Keep A Multi-valued Parameter Cleared From Selections Until The User Select His Choice

Apr 28, 2008

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.

View 5 Replies View Related

DB Engine :: Not Able To Shrink Log File

Nov 24, 2015

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

View 9 Replies View Related

DB Engine :: Deleting A Log File Permanently?

Jul 6, 2015

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?

View 12 Replies View Related

DB Engine :: Trace File Rollover

Nov 4, 2015

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.

View 6 Replies View Related

DB Engine :: Restore Database Only With MDF File?

Jun 25, 2015

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.

View 4 Replies View Related

DB Engine :: Cannot Restore BAK File On Server 2012

Aug 6, 2015

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)

View 4 Replies View Related

DB Engine :: Can't Shrink MSDB Log File - Data Is 3GB But LDF Is 300 GB

Sep 26, 2015

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.

View 7 Replies View Related

DB Engine :: Copy Database File To Different Drive?

Jun 7, 2015

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.

View 12 Replies View Related

DB Engine :: Cannot Remove One Last Data File From TempDB

May 19, 2015

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.

I have also tried using the command:

ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev#, FILENAME = '<new location');

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.

View 2 Replies View Related

DB Engine :: Analyzing What Is Causing The Log File To Grow

Aug 20, 2015

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.

View 8 Replies View Related

DB Engine :: How To Remove Log File Older Than X Days

Apr 20, 2015

Log file rotation and cleanup script and how to remove the log file older then x days.

View 7 Replies View Related

DB Engine :: How To Pass CSV File Dynamically From A Path

Jul 14, 2015

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.

View 2 Replies View Related

DB Engine :: How To Copy File From Firebird Database To Server

Apr 23, 2015

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.

View 8 Replies View Related

DB Engine :: Log File Error While Running A Stored Procedure?

Nov 2, 2015

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. 

View 2 Replies View Related

DB Engine :: Restore Backup File To Remote Server

Jul 8, 2015

I am working on a project for an SQL job. I am:

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. 

View 11 Replies View Related

DB Engine :: Extracting Server Event Log Into Text File

Sep 15, 2015

I would like to extract SQL server (event) log information into text file or DB tables so that we can read it with a script everyday.

View 13 Replies View Related

DB Engine :: How To Change Location Of Secondary Data File

Nov 9, 2015

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??

View 3 Replies View Related

DB Engine :: Splitting Data Of Single Large MDF File

Nov 13, 2015

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)

View 10 Replies View Related

DB Engine :: Why Does Log File Grow When Recovery Mode Is Simple

Jul 30, 2015

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?

View 11 Replies View Related

DB Engine :: Adding Multiple Log File In Existing Database

Jun 30, 2015

Can we add multiple log file in existing database?

View 8 Replies View Related

DB Engine :: 2008 R2 - Shrink Data File Size?

Nov 9, 2015

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?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved